Send email with attachment [Variable filename & subject]

Creating a macro - Writing a Script - Using the API

Send email with attachment [Variable filename & subject]

Postby wchan83 » Sun Sep 20, 2015 6:12 am

I'm new to excel macro.

I have a excel file that need to attach to a email and send to external everyday with lotus note.

- This excel file will change name frequently.
- Email subject will also change frequently.


I followed internet source, now I can send out email with attachment to external as following:
Code: Select all   Expand viewCollapse view
Sub Send_Email_via_Lotus_Notes()
    Dim Maildb As Object
    Dim MailDoc As Object
    Dim Body As Object
    Dim Session As Object
    'Start a session of Lotus Notes
        Set Session = CreateObject("Lotus.NotesSession")
    'This line prompts for password of current ID noted in Notes.INI
        Call Session.Initialize
    'or use below to provide password of the current ID (to avoid Password prompt)
        'Call Session.Initialize("<password>")
    'Open the Mail Database of your Lotus Notes
        Set Maildb = Session.GETDATABASE("", "D:\Notes\data\Mail\eXceLiTems.nsf")
        If Not Maildb.IsOpen = True Then Call Maildb.Open
    'Create the Mail Document
        Set MailDoc = Maildb.CREATEDOCUMENT
        Call MailDoc.REPLACEITEMVALUE("Form", "Memo")
    'Set the Recipient of the mail
        Call MailDoc.REPLACEITEMVALUE("SendTo", "Ashish Jain")
    'Set subject of the mail
        Call MailDoc.REPLACEITEMVALUE("Subject", "Subject Text")
    'Create and set the Body content of the mail
        Set Body = MailDoc.CREATERICHTEXTITEM("Body")
        Call Body.APPENDTEXT("Body text here")
    'Example to create an attachment (optional)
        Call Body.ADDNEWLINE(2)
        Call Body.EMBEDOBJECT(1454, "", "C:\dummy.txt", "Attachment")
    'Example to save the message (optional) in Sent items
        MailDoc.SAVEMESSAGEONSEND = True
    'Send the document
    'Gets the mail to appear in the Sent items folder
        Call MailDoc.REPLACEITEMVALUE("PostedDate", Now())
        Call MailDoc.SEND(False)
    'Clean Up the Object variables - Recover memory
        Set Maildb = Nothing
        Set MailDoc = Nothing
        Set Body = Nothing
        Set Session = Nothing
End Sub

However, the attachment filename must be a static path & pre-defined, the email subject also hard-coded.
Is there way that I can modify/ change such that it more dynamic, such as asking for input in the excel file ?

Please provide some guide, sorry I'm new to VBA. Thanks :)
OpenOffice 3.1 on Windows 10
wchan83
 
Posts: 1
Joined: Sun Sep 20, 2015 5:58 am

Re: Send email with attachment [Variable filename & subject]

Postby FJCC » Sun Sep 20, 2015 3:50 pm

On this forum we support OpenOffice and other products descended from OpenOffice.org. Excel is a completely different program. Someone here may be able to help, but your chances of getting an answer would be better on a forum dedicated to Excel or Visual Basic.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7257
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Send email with attachment [Variable filename & subject]

Postby Kenneth08 » Tue Aug 29, 2017 8:43 am

What is your problem with the code?
Ubuntu 15.04
LibreOffice 4.4.2.2
Evolution 3.12.11
User avatar
Kenneth08
 
Posts: 12
Joined: Fri Aug 25, 2017 4:02 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests