Creating HTML Emails From Access using VBAVery similarily to the VBA Outlook Automation procedure, one need only make a slight modification.
Simply replace the line:
.Body = strBody
with
.HTMLBody = strBody
In which case, the sBody must now be in HTML format “
Your content goes here” . Do not forget the HTML and BODY tags. The .HTMLBody can use most basic HTML Tags so go wild creating your messages (<i></i>, <b></b>, <br />, <p>, <h1>…<h6>, <a href>, <img>, <src>, …)So our new HTML Email procedure would be:
'---------------------------------------------------------------------------------------
' Procedure : SendHTMLEmail
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Automate Outlook to send an HTML email with or without attachments
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding version -> None required
' Early Binding version -> Ref to Microsoft Outlook XX.X Object Library
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTo : To Recipient email address string (semi-colon separated list)
' sSubject : Text string (HTML) to be used as the email subject line
' sBody : 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
' vCC : CC Recipient email address string (semi-colon separated list)
' vBCC : BCC Recipient email address string (semi-colon separated list)
' vAttachments : Array of attachment (complete file paths with
' filename and extensions)
' vAccount : Name of the Account to use for sending the email (normally the e-mail adddress)
' if no match is found it uses the default account
'
' Usage:
' ~~~~~~
' Call SendHTMLEmail("abc@xyz.com", "My Subject", "My body.", True)
' Call SendHTMLEmail("abc@xyz.com;def@wuv.ca;", "My Subject", "My body.", True)
' Call SendHTMLEmail("abc@xyz.com", "My Subject", "My body.", True, , _
' Array("C:\Temp\Table2.txt"))
' Call SendHTMLEmail("abc@xyz.com", "My Subject", "My body.", True, , _
' Array("C:\Temp\Table2.txt", "C:\Temp\Supplier List.txt"))
' Call SendHTMLEmail("abc@xyz.com", "My Subject", "My body.", True, , _
' Array("C:\Temp\Table2.txt", "C:\Temp\Supplier List.txt"), _
' "cde@uvw.com")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2007-11-16 Initial Release
' 2 2017-02-15 Added retention of default e-mail signature
' Added conditional compiler directives for early and
' late binding
' 3 2019-01-20 Updated Copyright
' Added usage examples
' Added sAccount option
' 4 2019-09-06 Updated the handling of sTo and sBCC to split e-mail
' addresses into individual recipients and
' improved error reporting for unresolvable e-mail
' addresses per an issue flagged by InnVis (MSDN)
' 5 2020-03-12 Bugs fixes (missing declarations) from comments by
' S.A.Marshall in answers forum.
' Added CC to function
'---------------------------------------------------------------------------------------
Function SendHTMLEmail(ByVal sTo As String, _
ByVal sSubject As String, _
ByVal sBody As String, _
ByVal bEdit As Boolean, _
Optional vCC As Variant, _
Optional vBCC As Variant, _
Optional vAttachments As Variant, _
Optional vAccount As Variant)
On Error GoTo Error_Handler
' #Const EarlyBind = 1 'Use Early Binding
#Const EarlyBind = 0 'Use Late Binding
#If EarlyBind Then
Dim oOutlook As Outlook.Application
Dim oOutlookMsg As Outlook.MailItem
Dim oOutlookInsp As Outlook.Inspector
Dim oOutlookRecip As Outlook.Recipient
Dim oOutlookAttach As Outlook.Attachment
Dim oOutlookAccount As Outlook.Account
#Else
Dim oOutlook As Object
Dim oOutlookMsg As Object
Dim oOutlookInsp As Object
Dim oOutlookRecip As Object
Dim oOutlookAttach As Object
Dim oOutlookAccount As Object
Const olMailItem = 0
Const olFormatHTML = 2
#End If
Dim aRecip As Variant
Dim i As Integer
Set oOutlook = CreateObject("Outlook.Application")
Set oOutlookMsg = oOutlook.CreateItem(olMailItem)
With oOutlookMsg
.display 'Had to move this command here to resolve a bug only existent in Access 2016!
'TO
aRecip = Split(sTo, ";")
For i = 0 To UBound(aRecip)
If Trim(aRecip(i) & "") <> "" Then
Set oOutlookRecip = .Recipients.Add(aRecip(i))
oOutlookRecip.Type = 1
End If
Next i
'CC
If Not IsMissing(vCC) Then
aRecip = Split(vCC, ";")
For i = 0 To UBound(aRecip)
If Trim(aRecip(i) & "") <> "" Then
Set oOutlookRecip = .Recipients.Add(aRecip(i))
oOutlookRecip.Type = 2
End If
Next i
End If
'BCC
If Not IsMissing(vBCC) Then
aRecip = Split(vBCC, ";")
For i = 0 To UBound(aRecip)
If Trim(aRecip(i) & "") <> "" Then
Set oOutlookRecip = .Recipients.Add(aRecip(i))
oOutlookRecip.Type = 3
End If
Next i
End If
.Subject = sSubject
Set oOutlookInsp = .GetInspector 'Retains the signature if applicable
.HTMLBody = sBody & .HTMLBody
.BodyFormat = olFormatHTML
.Importance = 1 'Importance Level 0=Low,1=Normal,2=High
If Not IsMissing(vAccount) Then
For Each oOutlookAccount In oOutlook.Session.Accounts
If oOutlookAccount = vAccount Then
Set oOutlookMsg.SendUsingAccount = oOutlookAccount
End If
Next
End If
' Add attachments to the message.
If Not IsMissing(vAttachments) Then
If IsArray(vAttachments) Then
For i = LBound(vAttachments) To UBound(vAttachments)
If vAttachments(i) <> "" And vAttachments(i) <> "False" Then
Set oOutlookAttach = .Attachments.Add(vAttachments(i))
End If
Next i
Else
If vAttachments <> "" Then
Set oOutlookAttach = .Attachments.Add(vAttachments)
End If
End If
End If
For Each oOutlookRecip In .Recipients
If Not oOutlookRecip.Resolve Then
'You may wish to make this a MsgBox! to show the user that there is a problem
Debug.Print "Could not resolve the e-mail address: ", oOutlookRecip.Name, oOutlookRecip.Address, _
Switch(oOutlookRecip.Type = 1, "TO", _
oOutlookRecip.Type = 2, "CC", _
oOutlookRecip.Type = 3, "BCC")
bEdit = True 'Problem so let display the message to the user so they can address it.
End If
Next
If bEdit = True Then 'Choose btw transparent/silent send and preview send
'.Display 'Preview
Else
.send 'Automatically send the e-mail w/o user intervention
End If
End With
Error_Handler_Exit:
On Error Resume Next
If Not oOutlookAccount Is Nothing Then Set oOutlookAccount = Nothing
If Not oOutlookAttach Is Nothing Then Set oOutlookAttach = Nothing
If Not oOutlookRecip Is Nothing Then Set oOutlookRecip = Nothing
If Not oOutlookInsp Is Nothing Then Set oOutlookInsp = Nothing
If Not oOutlookMsg Is Nothing Then Set oOutlookMsg = Nothing
If Not oOutlook Is Nothing Then Set oOutlook = Nothing
Exit Function
Error_Handler:
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."
Else
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: SendHTMLEmail" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
End If
Resume Error_Handler_Exit
End Function
Important Note Regarding the #Const EarlyBind variable
Normally the
' #Const EarlyBind = 1 'Use Early Binding
#Const EarlyBind = 0 'Use Late Binding
would be declared publicly in a standard module and not just in a single procedure. Basically, during development, you’d use Early Binding to facilitate coding and have access to Intellisense, and then when you are testing and deploying your solution to your end-users you would switch over to using Late binding to minimize versioning issues with reference libraries.
Adding a Read Receipt
Some people have asked me how to add a read receipt to this code. It is very simple to do, simply add the following (perhaps under the .Importance line):
.ReadReceiptRequested = True
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?
The Story Continues…
Since I first published this post, I have made further improvements. As such, you may like to look over my 2nd part to this post VBA – Send HTML Emails Using Outlook Automation – Improved.
Hi,
This looks great, just a question; does this work with outlook/access 2010?
Thanks and kind regards
Dan
Yes, it does.
I have been using this function (or a variation thereof) since Access 2000 (2000, 2003, 2007, 2010) without any problem.
Hi,
I am struggling to get this to work with an attachment it falls over with a 13 – type mismatch on
If AttachmentPath “” And AttachmentPath(i) “False” Then
this line, if i drag the coding down to the next line and let the coding attach the file it works, and the email is sent with the attachment.
The attachment is a network drive using variables to find the specific data and month for where to get the file.
I just cant get it to add the file.
any ideas
You don’t give an example of how you are calling the function, so it is very difficult to help troubleshoot the problem. My best guess would be that you are not supplying the AttachmentPath input variable with an array of the attachments to include in the email. Perhaps you are trying to pass a string instead. Make sure you are in fact passing it an array.
You may ask why I chose an array, because this allows for multiple attachments.
If I add more recipients i will get an error.
Outlook does not recognize one or more names. How pass more recipients without the problems. Thank you.
I’m calling the function:
strBody = “Hello world!!!”
Call SendHTMLEmail(“‘mail1@mail1.com;mail2@mail2.com”, “Subject test”, strBody, False)
In that case, one solution would be to change the strTo input variable to a Variant and pass it an array argument and loop through each element in the passed array to perform an .Recipients.Add(). So you’d do something like:
Function SendHTMLEmail(strTo As Variant, 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
If IsArray(strTo) Then
For i = LBound(strTo) To UBound(strTo)
Set objOutlookRecip = .Recipients.Add(strTo(i))
objOutlookRecip.Type = 1
Next i
Else
Set objOutlookRecip = .Recipients.Add(strTo)
objOutlookRecip.Type = 1
End If
If Not IsMissing(strBCC) Then
Set objOutlookRecip = .Recipients.Add(strBCC)
objOutlookRecip.Type = 3
End If
.Subject = strSubject
.HTMLBody = 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) – 1
If AttachmentPath(i) <> “” And AttachmentPath(i) <> “False” Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath(i))
End If
Next i
Else
If AttachmentPath <> “” And AttachmentPath(i) <> “False” 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
And you could do the same for BCC, …
Hello,
would it be possible to add the content of a folder, rather than pre-specified files?
Thanks a lot in advance,
J.
I don’t see why not. You’d have to change the code slightly to loop through the content of a specified folder and add each item as an attachment.
Hi Daniel
Would be of great help if you implemented code to “slightly to loop through the content of a specified folder and add each item as an attachment.”
into your SendHTMLEmail
Rather than use a string as the body of the email, I would like to use either a .OFT file or HTML.
Do you have the code for either of these options?
I’m not familiar with OFT, but HTML is simply text which is a string. So you can simply use your HTML string without any issue.
is it posssible to include an appointment in the email?
I found your procedure to create an appointment object, but don’t know how to include it in the SendHTMLEmail
function.
No, you wouldn’t modify this procedure, but rather use VBA to create the appointment and in the appointment set the Attendees and then send it (the appointment that is). Then the attendees would receive an e-mail with the appointment.
Sir!
I found your comment about the .Display that had to be moved into the first position of the With statement in order to work in 2016.
THANK YOU! My original code now works again.
B
Is there a way to suppress the outlook security message so there is no need for user interaction?
See: http://www.devhut.net/2010/09/03/outlook-automation-bypass-outlooks-security-prompt/
Hi, Thanks a lot for this 🙂 it works perfectly with attachments
Could you please let me know how to modified code if I want to send email WITHOUT attachments, but still want to use your function.
Call SendHTMLEmail(“RecipientEmail”, “Subject”, “EmailBody”, False, , array(“C:\Users\Test\Documents\sample.pdf”, “C:\Users\Test\Documents\ballons.gif”))
Thanks!
The attachment input variable is an optional variable, so just don’t include it if you want to omit any attachments. Something like:
Call SendHTMLEmail(“RecipientEmail”, “Subject”, “EmailBody”, False)
Thanks!!!
Is there a way to choose an specific mailbox to send the emails within this macro? Any code that help on that? Thank you.
That’s what the sAccount input variable is for.
For sBody variable, I refer it to a cell with line break and some format (e.g. bold) as below. But it turn to one line without any format. Please advise
Call SendHTMLEmail(“abc@xyz.com”, “My Subject”, Range(“B18:B18”), True)
That’s because, as usual, Microsoft is using their own formatting which is not HTML. SO you have to use a function to convert the Excel formatting to proper HTML. There are several thread on this, such as:
https://social.msdn.microsoft.com/Forums/fr-FR/626a351a-de17-4389-9ad6-a2be20ce2fd9/convert-contents-of-a-formatted-excel-cell-to-html-format?forum=isvvba
Daniel,
I’m can usually muddle my way through access, but I’m having issues. I’ve been running your email routine here for over a year now. And I use for both email and txt sending (email–>text portal). My email messages I have a routine that goes through and sends to everyone in my customer database in a defined area. WOrks well for updating customers. On almost every message, I have been doing “preview” so it leaves it open in outlook, and then I manually attach a simple jpg. I didn’t take the time when I set this up a year ago to use attachments through your snippet of code.
NOW, I’m trying to modify things to always add the local file to all email messaeges.
I’ve tried various things:
THIS WORKS JUST FINE WITHOUT ATTACHMENTS:
Call SendHTMLEmail(Me.txt_To, Me.txt_Subject, Me.txt_Msg, Me.chk_Edit, Me.txt_BCC)
Call SendHTMLEmail(Me.txt_To, Me.txt_Subject, Me.txt_Msg, Me.chk_Edit, Me.txt_BCC, “C:\price.jpg”)
Call SendHTMLEmail(Me.txt_To, Me.txt_Subject, Me.txt_Msg, Me.chk_Edit, Me.txt_BCC, array(“C:\price.jpg”))
txt_attach = “c:\price.jpg”
MsgBox (“” & [txt_attach] & “”)
‘msg box returns: c:\price.jpg
Call SendHTMLEmail(Me.txt_To, Me.txt_Subject, Me.txt_Msg, Me.chk_Edit, Me.txt_BCC, [txt_attach])
I’ve gotta me missing something simple…. It sends the message to outlook, but doesn’t send it… comes up with errors…
Try
Hi Daniel,
Would it be possible to use a query “qryMailDetails” to collect details like TO, CC, Order…..
Public Function Test()
Dim strTO As String
Dim rst As DAO.Recordset
Set rst = DBEngine(0)(0).OpenRecordset(strSQL1)
strTO = rst!To
Call SendHTMLEmail(strTO, “Order”, “My body.”, True)
End Function
Sure, I’ve incorporated this function within numerous query recordset loops. Works great.
No need to assign the query values to a variable, just pass them straight to the function
Thanks Daniel,
I use code below and this works great if I have 2 e-mail addresses (To & CC), but I get an error when I only have 1 e-mail address (TO).
Not every contact has 2 e-mail address, so how could I fix this error?
Call SendHTMLEmail(sMailTO, sSubject, sMessage, True, sMailCC)
The code is for single e-mail addresses.
What error are you getting exactly?
What are the values of your input variables that you are passing to the function?
I get a message box: An Error Has Occurred!
Error number: 440
Error Source: SendHTMLEmail
Error Description: There must be at least one name or contact group in the To,Cc, or Bcc box.
I get all inputs from a form, and I have always a TO e-mail address, but not always a CC e-mail address. So the CC fields can be null.
The issue here is that you are passing a “” value for BCC and outlook can’t resolve that
You could replace
If Not IsMissing(sBCC) Then Set oOutlookRecip = .Recipients.Add(sBCC) oOutlookRecip.Type = 3 End Ifwith something along the lines of
If Not IsMissing(sBCC) Then If Len(Trim(sBCC & vbNullString)) > 0 Then Set oOutlookRecip = .Recipients.Add(sBCC) oOutlookRecip.Type = 3 End If End Ifand see if that remedies the situation for you.
Is the importance level an integer? I’d like to be able to set the importance at runtime, but I want to make sure I set the parameter up correctly.
I’m not sure. None of the documentation I’ve come across tells us. Normally I declare them as Const
You could create an Enum and do something along the lines of
Enum OutlookImportanceLevel olImportanceLow = 0 olImportanceNormal = 1 olImportanceHigh = 2 End Enum Function SendHTMLEmail(ByVal sTo As String, _ ByVal sSubject As String, _ ByVal sBody As String, _ ByVal bEdit As Boolean, _ Optional sBCC As Variant, _ Optional aAttachments As Variant, _ Optional sAccount As Variant, _ Optional ImportanceLvl As OutlookImportanceLevel = olImportanceNormal) On Error GoTo Error_Handler ' #Const EarlyBind = 1 'Use Early Binding #Const EarlyBind = 0 'Use Late Binding #If EarlyBind Then Dim oOutlook As Outlook.Application Dim oOutlookMsg As Outlook.MailItem Dim oOutlookInsp As Outlook.Inspector Dim oOutlookRecip As Outlook.Recipient Dim oOutlookAttach As Outlook.Attachment Dim oOutlookAccount As Outlook.Account #Else Dim oOutlook As Object Dim oOutlookMsg As Object Dim oOutlookInsp As Object Dim oOutlookRecip As Object Dim oOutlookAttach As Object Dim oOutlookAccount As Object Const olMailItem = 0 #End If Dim i As Integer Set oOutlook = CreateObject("Outlook.Application") Set oOutlookMsg = oOutlook.CreateItem(olMailItem) With oOutlookMsg .Display 'Had to move this command here to resolve a bug only existent in Access 2016! Set oOutlookRecip = .Recipients.Add(sTo) oOutlookRecip.Type = 1 If Not IsMissing(sBCC) Then Set oOutlookRecip = .Recipients.Add(sBCC) oOutlookRecip.Type = 3 End If .Subject = sSubject Set oOutlookInsp = .GetInspector 'Retains the signature if applicable .HTMLBody = sBody & .HTMLBody .Importance = ImportanceLvl 'Importance Level 0=Low,1=Normal,2=High If Not IsMissing(sAccount) Then For Each oOutlookAccount In oOutlook.Session.Accounts If oOutlookAccount = sAccount Then Set oOutlookMsg.SendUsingAccount = oOutlookAccount End If Next End If ' Add attachments to the message. If Not IsMissing(aAttachments) Then If IsArray(aAttachments) Then For i = LBound(aAttachments) To UBound(aAttachments) If aAttachments(i) <> "" And aAttachments(i) <> "False" Then Set oOutlookAttach = .Attachments.Add(aAttachments(i)) End If Next i Else If aAttachments <> "" And aAttachments(i) <> "False" Then Set oOutlookAttach = .Attachments.Add(aAttachments) End If End If End If For Each oOutlookRecip In .Recipients If Not oOutlookRecip.Resolve Then oOutlookMsg.Display End If Next If bEdit = True Then 'Choose btw transparent/silent send and preview send '.Display Else .Send End If End With Error_Handler_Exit: On Error Resume Next If Not oOutlookAccount Is Nothing Then Set oOutlookAccount = Nothing If Not oOutlookAttach Is Nothing Then Set oOutlookAttach = Nothing If Not oOutlookRecip Is Nothing Then Set oOutlookRecip = Nothing If Not oOutlookInsp Is Nothing Then Set oOutlookInsp = Nothing If Not oOutlookMsg Is Nothing Then Set oOutlookMsg = Nothing If Not oOutlook Is Nothing Then Set oOutlook = Nothing Exit Function Error_Handler: 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." Else MsgBox "The following error has occured" & vbCrLf & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ "Error Source: SendHTMLEmail" & vbCrLf & _ "Error Description: " & Err.Description & _ Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _ , vbOKOnly + vbCritical, "An Error has Occured!" End If Resume Error_Handler_Exit End FunctionThis is great… I’m not able to choose the sending account even when I Call SendHTMLEmail(myTo, mySubj, myBody, True, , , myFrom) … Outlook uses my default email account. I’ve set myFrom as a variant but no luck; your sub runs fine, I just get a wrong sending address. Is there an easy solution?
Daniel… I used this code from rondebruin and realized the account I needed was not showing up in the message box his code calls ( https://www.rondebruin.nl/win/s1/outlook/account.htm ), although several others were showing up; reason was because I had not closed and reopened Outlook prior to running the rondebruin script, and AFTER having added the new email account I was trying to send from. I have not retried your code above since restarting Outlook, but think this may be the cause of my “sendusingaccount” issue. Thanks for sharing your expertise and this great code. Have a good day.
Just two big thumbs up on this! Some awesome sauce. Worked great for me and solves a big problem – being able to send the email through the user’s inbox so it shows up under Sent Items, etc.
Thanks for the detailed post oh so many years ago!
Dear Daniel Pineault,
Thanks for your kind great information, experience, support, …. etc.
Really I used your code and read all the support you give to the other
you are a great man..
thanks again.
Hi,
This is great code and does just what I need (I have the TO: email address in a text box value and I’m able to refer to this value as txtEmailAddress and BCC Fixed email addresses 🙂
One thing I’m struggling however is trying to figure out if I can add a report on the fly to the email generated.
For example I have a report which is based on the same form I’ve created a button to generate an email from and would love it if I can attached that report without having it saved anywhere
You have to first generate your report as a PDF (or whatever format you want) and that means saving it to the HD (you can simply use the TEMP dir for this), attach it to your email, send your email, then delete the PDF from the TEMP dir.
Hello,
I am new to writing VBA. How would I go about pulling the email address from a form that an individual is entering data into?
Thank you,
You should be simply able to use
Me.EmailAddressControlName
Hi, I copied your code into my ms access 2019 program but when I do a Debug/Compile I get a compile error: olImportanceNormal variable not defined. When I comment the line that contains that, I get a similar compile error: ImportanceLvl variable not defined and so on. Could it be I miss the proper References? In my Tools/References I have :
Visual Basic for Applications
Microsoft Access 16.0 Object Library
OLE Automation
Microsoft Office 16.0 Access Database Engine Object Library
Did you copy the Enum when you copied the function?
No I did not
Thanks for the response.
No I did not.
But when I do, it gives a Compile error only on ImportanceLvl (variable not defined)
I’m not sure what to say. If you copy the Enum at the top of your module and copy the function into that same module, it compiles without issue and works.
I’d suggest you create a new Module, copy/paste the code and compile it. It won’t generate any errors.
But when I do, it gives a Compile error only on ImportanceLvl (variable not defined)
Dear Daniel thank you for your valuable assistance in solving this. Indeed, I created a new module and now there is no compilation error.
Thank you
Best regards and best wishes
MN
Hi Daniel, I have an issue where I created a button on an Microsoft® Access® 2019 MSO (Version 2211 Build 16.0.15831.20098) 32-bit form containing a command (talk from a youtube video) to send an email via Microsoft® Outlook® 2019 MSO (Version 2211 Build 16.0.15831.20098) 32-bit which worked fine until we recently modified out outlook profiles to use the 365 server as opposed to the local one. I not either receive a message stating Microsoft Visual Basic Run-time error ‘2147023728 (80070490)’: Element not found or after trying to amend the code Microsoft Visual Basic Run-time error ‘429’: ActiveX component can’t create object.
Original Code
Dim Msg As String
Msg = “Hi ” & Assigned_to & “” & _
“A New Action Has Been Rasied By ” & Raised_By & “” & _
“Issue = ” & Issue & “” & _
“Action = ” & Action & “” & _
“Required By = ” & Required_By & “”
‘Remember to add REFERENCE to Microsoft Outlook Object Library
Dim O As Outlook.Application
Dim M As Outlook.MailItem
Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)
With M
.BodyFormat = olFormatHTML
.HTMLBody = Msg
‘.Body = Txt – if you use olFormatPlain
.CC = RaisedByEmail
.To = AssignedToEmail
.BCC = “x.x@x.co.uk;x.x@rockoil.co.uk”
.Subject = “Updated Action ” & ID
.Display
‘.Send
End With
Set M = Nothing
Set O = Nothing
End Sub
Amended Code
Private Sub Command120_Click()
Dim O As Object, M As Object
Dim Msg As String
Msg = “Hi ” & Assigned_to & “” & _
“A New Action Has Been Rasied By ” & Raised_By & “” & _
“Issue = ” & Issue & “” & _
“Action = ” & Action & “” & _
“Required By = ” & Required_By & “”
Set O = CreateObject(“Outlook.Application”)
Set M = O.CreateItem(olMailItem)
‘M.Display
‘signature = M.body
With M
‘.BodyFormat = olFormatHTML
.HTMLBody = Msg
‘.Body = Txt – if you use olFormatPlain
.CC = RaisedByEmail
.To = AssignedToEmail
.BCC = “x.x@x.co.uk;x.x@rockoil.co.uk”
.Subject = “Updated Action ” & ID
.Display
‘.Send
End With
Set M = Nothing
Set O = Nothing
End Sub
I am not a VB programmer so I’m completely lost on what is needed to be change to make this work again. Any Ideas?
Have you seen: https://www.devhut.net/createobjectoutlook-application-does-not-work-now-what/
I AM STRUGGLING WITH VBA I NEED THE CODE TO SEND AN EMAIL. I HAVE THOUSAND OF NAME AND I WOULD LIKE TO GROUP TOGETHER AN EMAIL BY THE SUPERVISORS FOR A REPORT HOW WOULD I DO THAT
I’d urge you to post your question in a forum.