There are times when as VBA programmers/Analyst we require to send e-mails after our analysis or dashboard development. This is very trivial that when a dashboard is completed, it is required to send to our seniors or the required recipients. So, there are times, when we've set the VBA program to automate the dashboard development and then we need to send that dashboard by attaching it to mail manually. Did this ever happened that you developed the dashboard and forgot to send to the requested/desired persons? Oops !
Now this article will help us in learning the automation of e-mailing the dashboard (or any file) using Lotus Notes and VBA. This is a referential program and we might need to change the appropriate variables in the program to make it run at our end.
With the introduction of Lotus Notes and Domino Release 5.0.2b, we now have the ability to manipulate the Domino object model via COM. As a result, we can use VBA to take advantage of Lotus/Domino services and databases.
To do so, however, we'll need Lotus Notes client version 5.02.b (or higher), Domino Designer client, or Domino Server. As with most Visual Basic object libraries, the programs need not be running to use them. Lotus has plans to make this runtime package distributable independent of its full software installation. Notice that before we can access any of the objects within the Notes Session, we must initialize a session first.
Anyway, let's directly do the practical which involves two simple steps:
1. Set a reference to "Lotus Notes Automation Classes". In the code window, go to Tool --> References --> Select 'Lotus Notes Automation Classes".
2. Use the following Visual Basic code to send a Notes e-mail message. The code includes examples of code to include an attachment and to save the sent message, which are both optional and can be removed if desired. Don't forgot to replace the underlined phrases with your ones.
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
Now this article will help us in learning the automation of e-mailing the dashboard (or any file) using Lotus Notes and VBA. This is a referential program and we might need to change the appropriate variables in the program to make it run at our end.
With the introduction of Lotus Notes and Domino Release 5.0.2b, we now have the ability to manipulate the Domino object model via COM. As a result, we can use VBA to take advantage of Lotus/Domino services and databases.
To do so, however, we'll need Lotus Notes client version 5.02.b (or higher), Domino Designer client, or Domino Server. As with most Visual Basic object libraries, the programs need not be running to use them. Lotus has plans to make this runtime package distributable independent of its full software installation. Notice that before we can access any of the objects within the Notes Session, we must initialize a session first.
Anyway, let's directly do the practical which involves two simple steps:
1. Set a reference to "Lotus Notes Automation Classes". In the code window, go to Tool --> References --> Select 'Lotus Notes Automation Classes".
2. Use the following Visual Basic code to send a Notes e-mail message. The code includes examples of code to include an attachment and to save the sent message, which are both optional and can be removed if desired. Don't forgot to replace the underlined phrases with your ones.
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
BONUS VBA MACRO: Excel 2003 Style menu in Excel 2007 / 2010
Are you going for an interview ?
3 things to remember before Excel VBA Interview
50 Excel VBA Interview questions
or looking for a job ?
Excel, Access, SQL, VBA, MIS, Reporting and Data Analysts Jobs
3 things to remember before Excel VBA Interview
50 Excel VBA Interview questions
or looking for a job ?
Excel, Access, SQL, VBA, MIS, Reporting and Data Analysts Jobs
Comments
comment this line and it will work
comment this line and it will work