Compose an email automatically

[camylarde]

Hello,

I have a macro that combines 4 different workbooks and produces two workbooks as a result. They need to be send to fixed recipients in an email as an attachment. Can somebody tip me onto how to create an email in LOTUS Notes using VBA macro that would contain these files?

I need following in the email:

Recipients - multiple emails in our private domain CC recipients - multiple
Msg Class - Proprietary
Subject - text

Message body - text, 2 attached files, text

I did find some advices on how to form email, but there wasn't enough info on how to alter them for this particular case. Ill manage to alter my data when I am tipped on the proper format of the commands.

As a bonus, can the email be only composed, and remain unsent in lotus, so that the user may verify what is in it, and click send manually?

Thank you a million for useful advice.

Regards,

Petr

 

[Trevor G]

Petr take a look a this link it will give you the code

http://vbadud.blogspot.com/2007/10/automate-lotus-notes-email-using-visual.html

 

[camylarde]

Hello Trevor,

thank you for the code, it seems friendly enough for me to work with the attachments.

I have a major problem, and two minor questions.

1) If i want to structure the body text, can I use multiple

Call Body.APPENDTEXT("Body text here")

lines to form the paragraphs? Will normal special characters like & vbcr & _
work as well?

2) Is this line actively sending the precomposed email?

Call MailDoc.SEND(False)

If so, how can I force the macro to run, and finish, and leave the email open in lotus for the end user to review it? Is this even possible?

These questions might be simple one o answer, but I don't know, because as the main problem below shows, I didn't manage to get my code to work yet.

The main problem I have is as follows:

The second line of the code:

Call Session.Initialize

gives me prompt to put user password, but this is where I got stucked. See, the dialogue is asking for the password for ID file located in path drive:\Notes6\user.id. THe funny part is that we no longer use that location for the file. I confirmed with IT guys that this was the location for ID file to be stored, but is no longer company-wide. I even have the old file present at that location, but the macro didn't pick up the correct location after I've moved the file to another place.

any suggestions how to inform the macro where to look for the user.id file?

Thanks a lot for continued support.

 

[Trevor G]

Petr,

It may be easier to assist you if you could show all the code you are planning to use. Highlight the lines you are concerned with in a colour.

 

[camylarde]

Trevor,

i couldn't reply earlier, didn't have access to the linked data for reference for some reason.

The code I'm using is as follows:

[COLOR=#ff6600][B]How to send Lotus Notes mail messages with Microsoft Visual Basic[/B][/COLOR]
 
[COLOR=#333333][I]The following Visual Basic code will send a Notes e-mail message. [/I][/COLOR]
[COLOR=#333333][I]The code includes examples of code to include an attachment[/I][/COLOR]
[COLOR=#333333][I]and to save the sent message, which are both optional [/I][/COLOR]
[COLOR=#333333][I]and can be removed if desired.[/I][/COLOR]
 
[COLOR=#333333][COLOR=#3333ff]Dim Maildb As Object[/COLOR]
[COLOR=#3333ff]Dim MailDoc As Object[/COLOR]
[COLOR=#3333ff]Dim Body As Object[/COLOR]
[COLOR=#3333ff]Dim Session As Object[/COLOR]
[COLOR=#3333ff]'Start a session to notes[/COLOR]
[COLOR=#3333ff]Set Session = CreateObject("Lotus.NotesSession")[/COLOR]
[COLOR=#3333ff]'This line prompts for password of current ID noted in Notes.INI[/COLOR]
[COLOR=#3333ff]Call Session.Initialize [/COLOR]
[COLOR=#3333ff]'or use below to supply password of the current ID[/COLOR]
[COLOR=#3333ff]'Call Session.Initialize("")[/COLOR]
[COLOR=#3333ff]'Open the mail database in notes[/COLOR]
[COLOR=#3333ff]Set Maildb = Session.GETDATABASE("", "c:\notes\data\mail\mymail.nsf")[/COLOR]
[COLOR=#3333ff]If Not Maildb.IsOpen = True Then[/COLOR]
[COLOR=#3333ff]Call Maildb.Open[/COLOR]
[COLOR=#3333ff]End If[/COLOR]
[COLOR=#3333ff]'Create the mail document[/COLOR]
[COLOR=#3333ff]Set MailDoc = Maildb.CREATEDOCUMENT[/COLOR]
[COLOR=#3333ff]Call MailDoc.ReplaceItemValue("Form", "Memo")[/COLOR]
[COLOR=#3333ff]'Set the recipient[/COLOR]
[COLOR=#3333ff]Call MailDoc.ReplaceItemValue("SendTo", "John Doe")[/COLOR]
[COLOR=#3333ff]'Set subject[/COLOR]
[COLOR=#3333ff]Call MailDoc.ReplaceItemValue("Subject", "Subject Text")[/COLOR]
[COLOR=#3333ff]'Create and set the Body content[/COLOR]
[COLOR=#3333ff]Set Body = MailDoc.CREATERICHTEXTITEM("Body")[/COLOR]
[COLOR=#3333ff]Call Body.APPENDTEXT("Body text here")[/COLOR]
[COLOR=#3333ff]'Example to create an attachment (optional)[/COLOR]
[COLOR=#3333ff]Call Body.ADDNEWLINE(2)[/COLOR]
[COLOR=#3333ff]Call Body.EMBEDOBJECT(1454, "", "C:\filename", "Attachment")[/COLOR]
[COLOR=#3333ff]'Example to save the message (optional)[/COLOR]
[COLOR=#3333ff]MailDoc.SAVEMESSAGEONSEND = True[/COLOR]
[COLOR=#3333ff]'Send the document[/COLOR]
[COLOR=#3333ff]'Gets the mail to appear in the Sent items folder[/COLOR]
[COLOR=#3333ff]Call MailDoc.ReplaceItemValue("PostedDate", Now())[/COLOR]
[COLOR=#3333ff]Call MailDoc.SEND(False)[/COLOR]
[COLOR=#3333ff]'Clean Up[/COLOR]
[COLOR=#3333ff]Set Maildb = Nothing[/COLOR]
[COLOR=#3333ff]Set MailDoc = Nothing[/COLOR]
[COLOR=#3333ff]Set Body = Nothing[/COLOR]
[COLOR=#3333ff]Set Session = Nothing[/COLOR]
 
[/COLOR]

I'm battling with the following advice section:

Note: The Visual Basic programmer needs to set the Reference to use Lotus Domino objects prior to implementing this function. To enable the Lotus Notes classes to appear in the Visual Basic browser, you must execute the following within VB: Select Tools, References and select the checkbox for 'Lotus Notes Automation Classes'. - I've done that.

The above code is from the IBM support. GETDATABASE given here is pointing to the sample MailDB; you need to change that to your DB.

You can do that by

[COLOR=#3333ff]UserName = Session.UserName[/COLOR]
[COLOR=#3333ff]MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"[/COLOR]
[COLOR=#3333ff]'Open the mail database in notes[/COLOR]
[COLOR=#3333ff]Set Maildb = Session.GETDATABASE("", MailDbName)[/COLOR]

My test code looks like this:

[COLOR=#333333]Dim Maildb As Object[/COLOR]
[COLOR=#333333]Dim MailDoc As Object[/COLOR]
[COLOR=#333333]Dim Body As Object[/COLOR]
[COLOR=#333333]Dim Session As Object[/COLOR]
 
[COLOR=#333333]Set Session = CreateObject("Lotus.NotesSession")[/COLOR]
[COLOR=#333333]Call Session.Initialize[/COLOR]
[COLOR=#333333]UserName = Session.UserName[/COLOR]
[COLOR=#333333]MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"[/COLOR]
Set Maildb = Session.GETDATABASE("", MailDbName)
 
If Not Maildb.IsOpen = True Then
     Call Maildb.Open
     End If
 
Set MailDoc = Maildb.CREATEDOCUMENT
Call MailDoc.ReplaceItemValue("Form", "Memo")
Call MailDoc.ReplaceItemValue("SendTo", "Recipient")
Call MailDoc.ReplaceItemValue("Subject", "Test Email #1")
Set Body = MailDoc.CREATERICHTEXTITEM("Body")
Call Body.APPENDTEXT("Body text here")
Call Body.ADDNEWLINE(2)
Call Body.EMBEDOBJECT(1454, "", "H:\attachment.xls", "Attachment")
MailDoc.SAVEMESSAGEONSEND = True
Call MailDoc.ReplaceItemValue("PostedDate", Now())
Call MailDoc.SEND(False)
 
Set Maildb = Nothing
Set MailDoc = Nothing
Set Body = Nothing
Set Session = Nothing
 
End Sub

If I step run the code, on teh line two (Call Session.Initialize) it hangs up with the following error:

"Notes error: could not open the ID file."

the thing is, it is looking at the old file location, where the ID file was located about a year ago before we moved to a different structure.