In this post, we will be demonstrating how to send an email from Outlook without showing Outlook.
The Code
The main changes will be to Section 4.
Public Function sendEmailsWithoutShowingOutlook(strSQL As String, strEmailField As String, _ strSubject As String, strMessage As String, _ Optional strAttachmentPath As String = "") As Boolean On Error GoTo ErrorHandler '''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' 1 '''''''''''''''''''' ''''''''''''''''''' Declarations ''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim olApp As Object Dim olMail As Outlook.MailItem Dim olRecipient As Outlook.Recipient Dim olAttachment As Outlook.Attachment Dim db As DAO.Database Dim rs As DAO.Recordset ' Create the Outlook session. '''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 'if outlook is open it will need to be run as administrator '''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Set olApp = New Outlook.Application Set olMail = olApp.CreateItem(olMailItem) ' Create the message. Set olMail = olApp.CreateItem(olMailItem) '''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' 2 '''''''''''''''''''' ''''''''''''''''''' Recipients ''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''''''' 'open up a recordset and move through it Set db = CurrentDb Set rs = db.OpenRecordset(strSQL) With rs If Not .BOF And Not .EOF Then .MoveLast .MoveFirst While (Not .EOF) ' Add the To recipient(s) to the message. Set olRecipient = olMail.Recipients.Add(.Fields(strEmailField)) olRecipient.Type = olTo .MoveNext Wend End If End With '''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' 3 '''''''''''''''''''' ''''''''''''''''''''' Message''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''''''' With olMail ' Set the Subject, Body, and Importance of the message. .Subject = strSubject .Body = strMessage .Importance = olImportanceHigh 'High importance ' Add attachments to the message. If strAttachmentPath <> "" Then Set olAttachment = .Attachments.Add(strAttachmentPath) End If ' Resolve each Recipient's name. For Each olRecipient In .Recipients olRecipient.Resolve Next '''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' 4 '''''''''''''''''''' ''''''''''''''' Display, Save, Send''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''''''' .Save .Send End With sendEmailsWithoutShowingOutlook = True ExitFunction: Set olRecipient = Nothing Set olMail = Nothing Set olApp = Nothing Exit Function ErrorHandler: sendEmailsWithoutShowingOutlook = False Resume ExitFunction End Function
The Explanation
'''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''' 4 '''''''''''''''''''' ''''''''''''''' Display, Save, Send''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''''''' .Save .Send
The only significant change we have made is to remove .Display and add .Save and .Send.
Testing The Function
We don’t want to send 26 emails so we will use a SELECT statement in the form to retrieve only the first record from Customers.
Warning! Please alter the email address (use your own) in the E-Mail Address field of Customers.
Please fill out the form with the information below:
The SQL String is a simple SELECT Statement that will return the record associated with ID 1.
Clicking on Test will product the following result:
The Conclusion
In this post, we showed you how to send an email from Outlook without having to display Outlook. This can be useful in certain circumstances although it is generally a better idea to have Outlook visible with the fields filled out automatically as you will give yourself more control over the emailing process.
In the next post, we will be showing you how to send a report as an attachment.
