Send email with attachment [Variable filename & subject]

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
wchan83
Posts: 1
Joined: Sun Sep 20, 2015 5:58 am

Send email with attachment [Variable filename & subject]

Post by wchan83 »

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

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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Post by FJCC »

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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
Kenneth08
Posts: 12
Joined: Fri Aug 25, 2017 4:02 am

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

Post by Kenneth08 »

What is your problem with the code?
Ubuntu 15.04
LibreOffice 4.4.2.2
Evolution 3.12.11
Post Reply