---------------------------------------------------------------------------------------
' Procedure : SendEmail
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Automate Outlook to send emails with or without attachments
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strTo To Recipient email address string (semi-colon separated list)
' strSubject Text string to be used as the email subject line
' strBody Text string to be used as the email body (actual message)
' bEdit True/False whether or not you wish to preview the email before sending
' strBCC BCC Recipient email address string (semi-colon separated list)
' AttachmentPath single value or array of attachment (complete file paths with
' filename and extensions)
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2007-Nov-16 Initial Release
'---------------------------------------------------------------------------------------
Function SendEmail(strTo As String, strSubject As String, strBody As String, bEdit As Boolean, _
Optional strBCC As Variant, Optional AttachmentPath As Variant)
'Send Email using late binding to avoid reference issues
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim objOutlookAttach As Object
Dim i As Integer
Const olMailItem = 0
On Error GoTo ErrorMsgs
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add(strTo)
objOutlookRecip.Type = 1
If Not IsMissing(strBCC) Then
Set objOutlookRecip = .Recipients.Add(strBCC)
objOutlookRecip.Type = 3
End If
.Subject = strSubject
.Body = strBody
.Importance = 2 'Importance Level 0=Low,1=Normal,2=High
' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
If IsArray(AttachmentPath) Then
For i = LBound(AttachmentPath) To UBound(AttachmentPath)
If AttachmentPath(i) <> "" And AttachmentPath(i) <> "False" Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath(i))
End If
Next i
Else
If AttachmentPath <> "" Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If
End If
End If
For Each objOutlookRecip In .Recipients
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
If bEdit Then 'Choose btw transparent/silent send and preview send
.Display
Else
.Send
End If
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
Set objOutlookAttach = Nothing
ErrorMsgs:
If Err.Number = "287" Then
MsgBox "You clicked No to the Outlook security warning. " & _
"Rerun the procedure and click Yes to access e-mail " & _
"addresses to send your message. For more information, " & _
"see the document at http://www.microsoft.com/office" & _
"/previous/outlook/downloads/security.asp."
Exit Function
ElseIf Err.Number <> 0 Then
MsgBox Err.Number & " - " & Err.Description
Exit Function
End If
End Function
Update
Things have changed since the article was first published. If you are experiencing issues with the CreateObject(… line, then see my post entitled: CreateObject(“Outlook.Application”) Does Not Work, Now What?
This explanation and annotated code is the best I’ve ever seen in my 15 years of working with VBA. Plus,, the code provides for all options, incnluding the use of arrays to transmit multiple paths for attachments to each Outlook email.
Outstanding!.
I was hoping that in this code there would be a way to set a specific day of the week or some other date in correspondence with when to send this email. If I am missing something and it is up there please let me know. If it is not and anyone knows how to do so please help 🙂
Jacob,
You can set the .DeferredDeliveryTime property to delay sending an e-mail. Simplay add it within the ‘With objOutlookMsg’ section but before the ‘If bEdit Then’ line. So you’d add in something along the lines of:
.DeferredDeliveryTime = DateAdd(“ww”, 1, Date & ” 18:00″) ‘Send it one week from today a 6 o’clock in the evening
Hope this helps! At the very least, this should get you going in the right direction.
I am having some difficulty with the following line
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add (maria.w@wom.com)
objOutlookRecip.Type = 1
Says it is syntax error, can you help?
For one thing, your e-mail address needs to be surrounded by quotes.
Set objOutlookRecip = .Recipients.Add(“maria.w@wom.com”)
Thanks that is what I was missing 🙂
Glad I could help.
In the email, if I want to attach a form or query results how in this code would that be accomplished?
I am only guessing it is part of the attachment.
Well In your code I’d first export the query results to Excel (see: http://www.devhut.net/2012/04/19/ms-access-vba-export-records-to-excel/) and then attach that file to the e-mail.
I have put this Function as a public function within a module to be called by the on click event of a form button. I am getting an error message stating “The object doesn’t contain the automation object strTo.” Any ideas what this means?
Can you please provide me with your the code for your On Click event.
I have a problem when Outlook is initially closed. All is working fine if Outlook is open when i call SendEmail, but if Outlook is closed no recipients are added to the message. When new Outlook message is displayed only subject and attachment show in message.
Any suggestions on how i can get this to work when Outlook is closed when calling the Function?
Outlook is a programming PITA because it doesn’t act like the rest of the Office suite when it comes to Creating an Object. So you need some very special code. Please refer to: http://www.rondebruin.nl/win/s1/outlook/openclose.htm
Thank you! I’ve been scratching my head for some time now wondering what causes this to happen. So thank you, and Ron de Bruin, not only for the code, but for explaining why this is happening too:)
Thank you very much for this routine. It works very well except the email message is sent to the “Outbox”. Ant suggestions? Again, thank you for some great code.
What is the value of the bEdit input variable you are ussing? bEdit should be True if you wish to send it without user interaction, and False if you wish to edit the message manually first.
I had bought a commercial program that cost $500. GREAT program, except that my Access program is module based. Meaning that I have an ‘accdb’ front end that sets references and calls other ‘accde’ databases/modules. The problem I faced is that since the commercial program is a reference file located outside of my own program, it kept looking at the ‘accdb’ for the supporting table/query, and the report that I wanted to PDF and email. Your code allows me to embed the code within each individual database/module, so that it doesn’t have a problem recognizing that the report and supporting info is located in THAT database/module. Of course I have to customize a few things…, but I would have had to do that no matter what. Also, just wanted to mention, I added code to what you have that would also allow me to change the text in the ‘From’ box. So instead of showing just my email address in the ‘From’ box, it now shows variable text. For example: ‘Procent Advantage [aharvey@procentinc.com]’. You don’t see my email address until you double click the email message. Also, I add a kill statement in certain instances as I didn’t want to save the pdf. And example of this is emailing payroll stubs. Once sent, don’t want to save.
Again, thank you!!!!!, very much for posting this code! Immensely helpful!!!
Thank you – this is amazing!
This is very helpful. Thank you.
I can attach one file with no problem. However, I am having a problem with attaching multiple files, an array. Could you post an example of a properly formatted array?
Thank you again.
In its simplest form you could do something like:
Function TestSendHTMLEmail() Dim aFiles(2) As String aFiles(0) = "C:\Users\Daniel\Documents\SoftwareDevelopment.jpg" aFiles(1) = "C:\Users\Daniel\Documents\Bell_Too Slow.jpg" aFiles(2) = "C:\Users\Daniel\Documents\Bell-Usage Jan2nd.png" Call SendHTMLEmail("d_@hotmail.com", "Test", "This is a test!", True, , aFiles) End FunctionThat’s it! I didn’t realize I needed to create a separate Function for the array. Makes total sense now. I really appreciate the help. This is excellent coding. Thank you again.
Thanks Daniel,
very clear and easy to use. Straightforward result !
Thank you again
On Excel VBA I have the following code to get the File Path, is there anything like this for Access?
Sub BrowseForAttachment3()
Dim fd As Office.FileDialog
Dim Main As Worksheet
Dim File As Workbook
Dim strPath As String
Sheets(“Confirmation Checklist”).Select
Call PDFActiveSheet
Sheets(“Main”).Select
Set File = ThisWorkbook
Set Main = File.Sheets(“Main”)
strPath = File.Path
Set fd = Application.FileDialog(msoFileDialogFilePicker)
If strPath = “” Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & “\”
With fd
.AllowMultiSelect = False
‘ Set the title of the dialog box.
.Title = “Please select the file.”
.InitialFileName = strPath
‘ Clear out the current filters, and add our own.
.Filters.Clear
.Filters.Add “All Files”, “*.*”
‘ Show the dialog box. If the .Show method returns True, the
‘ user picked at least one file. If the .Show method returns
‘ False, the user clicked Cancel.
If .Show = True Then
Main.Cells(31, 2) = .SelectedItems(1) ‘replace txtFileName with your textbox
End If
End With
End Sub
The FileDialog works the same in Access. You might like to use a reusable function though, see: http://www.devhut.net/2016/10/04/late-binding-the-filedialog/.
I keep getting an error that states “compile errors: argument not optional” when I attempt to use this code. Any ideas as to what this is?
How are you trying to use the function exactly? Can you post your code?
Hi
Great code – thank you.
How do I save the emails to DRAFT rather than have them immediately sent out?
Easy! At the end of the code, instead of using .Display or .Send, use .Save
Also, you probably should use my latest version of Outlook automation, refer to VBA – Send HTML Emails Using Outlook Automation – Improved
Brilliant. So easy when you know! Thank you so much.
So very true!
Just found this and am extending my complements and gratitude for you sharing.
Thank you for this great coding. Everything was working well then I started getting an error trying to send to more than one email address. Outlook has no problem sending the email with one address. it’s when I try to add another (separated by ; ) it pops up an error: Checking Names… does not recognize the email address “name@address.com; name2@addess.com” . I unchecked check names in the options. (It will recognize the emails if I send it one at time). I can use one email in the to, one in the cc and one in the bcc, but when you add 2 or more Outlook displays the email and the error. Any thoughts or help is greatly appreciated.
It’s very hard to say without seeing the actual values, but in my experience it is an issue with the e-mail address as Outlook can’t resolve it. Perhaps try adding Trim() or check the value for hidden characters.
Also, verify your locale delimiter, perhaps it isn’t ;
I checked – it appears MS Outlook still uses the ; I did trim(), I even ensured no extra hidden characters. I even used 2 email addresses that are in my outlook address book. When the email opens and the Check Names box appears – I cancel out and the email addresses are exactly as I typed them no hidden spaces etc… Weirder yet – If I copy and paste the emails from the text field into Outlook manually it works. Outlook doesn’t like the way I’m passing the emails from Access.