Access/Outlook Email Automation

I've just discovered "Automation" and am trying to implement it in my Access d/b to send reports and external file attachments via e-mail. I'm not a VB programmer but I've searched on the web to find some code that mostly works OK (extract below) - I have 3 questions on this that I haven't been able to figure out:
1) Is it possible to set up ".Body" to the contents of an external file - eg: an Access report saved in RTF or HTML format, (or any other external file for that matter), rather than having to hard-code the text?
2) What changes need to be made to the code to add more than 1 attachment?
3) Is it possible to set up a link in the body of the email to open the attachments? eg: "click here to view your quotation".
Any help will be much appreciated.
With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
objOutlookRecip.Type = olTo

' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "This is the body of the message." & vbCrLf & vbCrLf

' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients

' Should we display the message before sending?
If DisplayMsg Then
End If
End With

Post your answer or comment

comments powered by Disqus

I'm trying to automate saving new emails from outlook inbox (with attachements) to Access and attachemnts to the local drive. I've figured out how to do this using a button in an access form to trigger this but is there a way for Outlook to trigger this process so it's automated?

Can outlook trigger code in access when I get any new mail?

Might be looking at this backwards.


I am aware of the Outlook.Application functionality.
Can VBA in Access be used to interact with other clients, such as Thunderbird, for example?
What about email automation on a Mac? I know there is no Access for Mac, but what about Excel email automation in Mac with clients other than outlook?
Thank you.

Hey guys,
Here is the issue. I am receiving emails (work related) in the same format (where the information is in the body of the Outlook email). Basically the emails contain some financial data that the team needs to manually enter in Access database.
The great question is - is there a way to somehow parse the emails and get the data from the email directly into an Access table?
The emails are coming into a group emailbox in Outlook.
Any assistance is greatly appreciated.


History -
I wrote the app in Access 2K and it sent email through Outlook97
We upgraded to Office 2003

I have a custome app in Access that utilizes the ability to send email through Outllook. It worked with Access2K and Outlook97 - it works in Access2K3 but Outlook gives a warning for every email that access is sending.

Outllook Warning: Another program is trying to send an email on your behalf. Do you want to let it? This may be a virus.

And then you have to wait like 5 seconds for the OK button to activate so you can let it send the mail. Is there any way around this? PLEASE TELL ME THERE IS

Here is how I am sending the mail...

Public Function SendEMail()
DoCmd.SendObject acSendNoObject, , , rs("Driver"), , , "TRUCK " & _
"UPDATE", EmailText, False
End Function

I have email automation established in VBA code. When I click a button on the database, it retrieves a list of email address from a table and automatically send the report via MS Outlook. It works fine on my PC. However, when I access the database from another PC and click the submit button it doesn't want to use Outlook. Instead it opens the Internet Connection Wizard. The PC is already connected to the internet.

I need it to recognize Outlook instead of trying to open Outlook Express.

Any ideas??

This is a strange one.

I have many computers using my database accross a workgroup.

Some PCs are win98, some winME, some XP. When my database wants to send emails automatically using the sendobject command all is fine apart from when the Xp machine does it.

It always defaults to the internet connection wizard and tries to follow this procedure through.

The outlook and email work fine using outlook normally, it just seems to be happending through automation.

does anyone know how to override this....

You could make my weekend very happy if you can.




I can generate an email by clicking on a button on a MS Access Report. In the body of the resulting Outlook email you have formatted text. I have also included an array of values which are displayed in this email but I need to highlight certain items in the array in red based on whether or not another column for that record is checked. Here is an image of the email. Notice that I have circled "ZWO" and that it is red. I need for this to happen programmically. Hope you can help. Thanks Linda
Attached Thumbnails     Reply With Quote 10-09-2012, 11:58 AM #2 rpeare VIP Windows XP Access 2003 Join Date Jul 2011 Posts 1,917 How are you currently generating this email, having it generate the body then manually changing the color yourself before sending?

I'm assuming you're using the sendobject method to generate the email?

If you are using sendobject I don't think you can do color formatting, you would have to blend html formatting (which I've never done) but here's an example using ASP which is similar to the code you'd use in a vb script

I've never done partial text formatting on email, I usually print reports to pdf's and email those out instead.

most of the stuff I looked at for your question was built around building an HTML based email rather than the sendobject command.

Hello, i have a button on my form that opens out an outlook email. Ont his part of the code:

With oMail
.To = Left$(strEMail, Len(strEMail) - 1) 'Remove Trailing ;
'.Body =

is where i enter what i want the message to display, however i cant get the formatting right. Im using access 97. ive attached a word doc which shows EXACTLY how id like the email to open up with. can anyone help me make it look like that? thanks! Attached Files email format.doc (22.0 KB, 7 views) Reply With Quote 11-23-2011, 12:33 PM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,129 This article has code relevant to Access97 up. It shows using HTML tags so should be possible.

If you need to learn about HTML tags check this site

Access 2007
Outlook 2007

I am totally new to VB and Access. I would like to create a command button that would open a new Outlook email with the Subject and body already filled in. I tried the wizard already but to no avail. Could someone please help me with this. Eventually I want to be able fill in the "To" line with addresses from a specific record in the database but for now I just want Access to open a new email in outlook. Obviously Im totally new to this so step by step would be helpful.


HI, hoping someone has something relatively simple, or can direct me to the right place, I want to open Outlook email application on click and attach a document (I have the full file path and file extension)

To date I've only ever used Docmd.sendobject , can I use this to send a document. My db isnt using actual attachments, I am just storing filepaths in a table. I guess then, next question is do I need to save the original document to the database first as some sort of temporary attachment perhaps?

I'm not interested in actually sending the email, or closing the app after, just to open if not already, add to: & subject: and attach a doc.

- - - - - - -

I've tracked down some code from the forums but I get error "user type not defined " on first line ... Dim Myoutlook as outlook.application

not a very good start I know, I am however using access 07 in case this is anything to do with, any help much appreciated

>> NOW FIXED ! -- In VBA tools, references menu I didnt have Microsoft Outlook 12.0 Object Library set, just checked the box and now all working fine! Code insert...

	Public Function Email_now()

Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim objOutlook As Object
Dim Attach As String

'Check if outlook if open
Set objOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If objOutlook Is Nothing Then
 ' Enter code to open outlook...
    'still working on this !!
    End If

' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application
' This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)

' This addresses it
MyMail.To = "test" '**put in reference to form

'MyMail.CC = MailList("Copy To")
'This gives it a subject
MyMail.Subject = "test" '**put in reference for subject
'This gives it the body
MyMail.Body = "test"
'If you want to send an attachment
'uncomment the following line
Attach = "C:usershome_steventest.txt"  '** add file path to attach doc
MyMail.Attachments.Add Attach

'This sends it!
'To Display instead of Send, Uncomment the next line
'And comment the "MyMail.Send" line above this.

'Cleanup after ourselves
Set MyMail = Nothing
'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.
Set MyOutlook = Nothing
End function

I have the following code that searches outlook emails for a specific word ("deadline" in this case) and then opens all emails that that contain that word. It all works perfectly, but if many emails are found, too many windows get opened.

Any suggestions how to modify this code, so it will just show and highlight all emails found in outlook (as if you would use the instant search feature in the attached picture).


	Private Sub Command84_Click()
On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim nms As Outlook.NameSpace
   Dim fldInbox As Outlook.MAPIFolder
   Dim highligh As Outlook.Search
   Dim msg As Outlook.MailItem
   Dim itm As Object

   Set nms = appOutlook.GetNamespace("MAPI")
   Set fldInbox = nms.GetDefaultFolder(olFolderInbox)

   For Each itm In fldInbox.Items
      If itm.Class = olMail Then
         Set msg = itm
         If InStr(msg.Body, "deadline") > 0 Then
         End If
      End If
   Next itm
   Exit Sub

   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

I was wondering if anyone has been able to copy an access report into the body of an outlook email.

I am tryin to copy only the active record. What I have done in the past was as follows:

Private Sub cmdEmailAssignedForm_Click()
Dim strWhere As String
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If
If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
strWhere = "[Improvement No] = " & Me.[Improvement No]
DoCmd.OpenReport "ReportEmailNoticeofAssigned", acViewPreview, , strWhere
End If
End Sub

I would like to improve this by just pasting the text on the active record into the email body.

Thanks for your help....


Hello everybody,

i want to make the following function in access: When i press a button, then outlook starts. i works great!! But now i want an existing word document as the body of the outlook email message

Code would be greatly appreciated



I am using the following code which successfully generates an outlook email message with a template. but when I add the myItem.Body I lose my template (which is basically a blank email with a sigature). Any clues?

rivate Sub CbSendEmail_Click()
If IsNull(Me.[Email Address]) Then
MsgBox "No Email Address For This Contact", , "No Valid Email Address"
Dim TEMPLATE As String
TEMPLATE = DLookup("EmailTemplate", "Tblconfiguration")
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItemFromTemplate(TEMPLATE)
myItem.Subject = "Integrasoft Ltd"
myItem.To = [Email Address]
End If
End Sub

In an Access 2007 application I'm trying to read the properties of Outlook email messages stored on a server in related project folders. I have utilized treeview and listview controls that allow the user to see all the files within various project folders, but when the file type is an Outlook message I'd like to be able to provide additional information like Sender, Recipient, Subject and Date. Any forum members experienced with this type of challenge? Any direction or advice is greatly appreciated.


Hi Forum, ms access 2010.
Email from access using Outlook Express works ok but one new pc (64 bit) is Windows 7 and I have just found out Outlook Express isn't supported by Windows 7.

Research indicates Windows 7 options are Outlook or Windows Live.

Appreciate some advice on issues and best road to go down where users have M Access 2010 on pc's using Windows XP and 64bit Windows 7.

Would I need to filter the users to use different email code depending on what pc they have ?

Thanks in advance

I can successfully create a table link from Access to Outlook for some folders in my Personal Outlook folder, but not for others. Access appears to create the link, but there is no data in the linked table, though in Outlook there is data.

Any ideas as to what I should check?

Hi All,

Apologies if this question has already been asked. Im a bit of a newb when it comes to Access. I was wondering if its possible to create a input form/email in Access/Outlook in which I can send to a number of people via email that populate a Access database/xls when they reply?

Extra Info:
-Users may have a different version of office that I have.
-I have office 97, 2000 and 2003.

Hope that all makes sense.


I was cleaning up a form and ms access suggested to optimize I should declare all variables. I placed the line. Option Explicit in doing so it forced me to Dim many variables in subsequently after doing this it wouldn't open ms outlook email and place the text in the specific email.

Am I doing something wrong as soon as I change it back to Option compare database and exclude the Dim lines ever thing works fine.

I know this is just being picky and thoughts?


FYI it isn't holding me up it is just increasing my education.

Hi All,
I have a MS Access project here that grabs the email address from the form and emails through Outlook, which works perfectly. What I require help with is that I would like to insert the contents of the report into the body of the email. So basically run the report, insert the content of the report in the body of the email and send. I'm not really sure how to get the data from the report into the body of the email.

Any help always appreciated.

River Attached Files MS Access to Email.mdb (288.0 KB, 3 views) Reply With Quote 04-28-2012, 05:45 AM #2 orange VIP Windows XP Access 2003 Join Date Sep 2009 Location Ottawa, Ontario, Canada Posts 4,207 I suggest you use Google to find related posts. Often people want to attach reports to emails.
I used this in Google "access to outlook with report in main body"

I don't think you can just copy a report as an email body, but I haven't tried it myself.

Here is a link that may get you started.

I found this code to embed one email in an outlook email using VB or VBA.

I also found this discussion but it didn't help me out very much since I could find the complete code anywhere as a final solution:

I'm wondering if anyone can help me out. I need to embed multiple images and the quantity will vary.

I also need to use late binding instead of early binding, if at all possible. We have different versions of Outlook within our organization.

Last but not least, I'd prefer to use Outlook Redemption Objects for this entire task but I don't know how to port this code to use Outlook Redemption Objects. If you can help me out with my first two questions, that would be great. Redemption Objects is not a necessity.

I have created several attachment fields in the Design View area of my table. I have chosed to use Hyperlink vs Attach to my dB to keep attachments separate from my BE File. I can see how to Hyperlink MS Word docs, MS Powerpoint docs, spreadsheet files, pictures, etc but have not figured out how to hyperlink to a MS Outlook email or Attach a specific email.

Is there a way to either Attach or Hyperlink to a MS Outlook email to my table records from design view or other way ? Printing the email and scanning it as a PDF and then hyperlinking to it seems as* backwards.

Thank you for your response ! Art

I'm using the following function to send Outlook Email

Public Function SendOutlookEmail(pasEmailAddress, pasEmailCC As Variant, pasEmailSubject As Variant, pasEmailMessage As Variant, pasSendEmail As Boolean) As Boolean
On Error GoTo Err_Proc

Dim NameSpace As Object
Dim EmailSend As Outlook.MailItem
Dim EmailApp As Object

Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)

EmailSend.To = Nz(pasEmailAddress, vbNullString)
EmailSend.CC = Nz(pasEmailCC, vbNullString)
EmailSend.Subject = Nz(pasEmailSubject, vbNullString)
EmailSend.Body = Nz(pasEmailMessage, vbNullString)

SendOutlookEmail = True
Set NameSpace = Nothing
Set EmailApp = Nothing

'-- Cleanup & Exit
myCall = vbNullString
Exit Function

SendOutlookEmail = False
Exit Function

End Function

Function works great and sends email. However I would like to give the user the option to preview the Email before it is sent. The last parameter (pasSendEmail) is intended to do this.

Is there a property for EmailSend to display the Email on the screen before it is send. The SendObject method has this option, so I thought that Outlook should have it too.

Thanks for your help!

hey guys

with access to word automation, im confused about the datasource to use. my understanding is that the datasource can either be a table or query. firstly, a query is not applicable for me. as for a table - this is where im getting confused.

I am trying to send the data as entered by a user in a form (and subform) to a word template (with bookmarks). because the data is only contained in the form, and has not yet been sent to the database, will i still be able to export the data from the form to the template?

i am reading up on past access to word automation posts, but i havent seen this yet..


Not finding an answer? Try a Google search.