By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,317 Members | 1,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,317 IT Pros & Developers. It's quick & easy.

How to use VBA to add Attachments to Emails

P: 16

I have a database where visit reports are attached to individual records. Field Name: "KAM Visit Report"

I want to have a macro button that will send an email with the report as an attachment.

Can I do this using the DoCmd coding?

Jun 29 '10 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 1,240
Be prepared for a very dark time in your life. No, just kidding, it's not that bad.

You will want to read about CDO emails versus using Outlook to send your emails. If you route the emails through Outlook, then it shows up in your Outlook Sent outbox and/or sent folder. But there's this goofy security warning message that pops up creating headaches, but thee's a little fix program for that, and maybe I'm forgetting some other little issues.

If you use CDO you may have to get with the network security guy to open a port for you and you'll have to let him know what user will be sending the CDO emails. It's not a good idea to open a port for just any/every user to do that.

I strongly prefer the CDO email method. Outlook was such a pain when I was trying to do it that way. Anyway, there's a lot of good discussion about the details of getting this done on See these threads:

One of these discussions mentions the lack of any notification that the mail was sent. I just include a .bcc so that all the cdo emails I send go to a mailbox where I can see what was sent and when. The .sender you use may get bounce notifications sometimes if a bad address shows up or a mailbox is full.

Jun 29 '10 #2

Expert 100+
P: 931
I'm not sure about email options other than Outlook, and so you should probably thoroughly look into Jim's CDO suggestions.

You can also look into the DoCmd.SendObject method...the VBA help page on SendObject is very thorough and it is a few minutes of reading that is well worth your time. This is a good option if you have not previously exported the report to a file somewhere outside of Access.

For a third possibility, if you want more fine-grained control over the process, the following will work with Outlook. Make sure you set a reference to the Microsoft Office Outlook library in the VBA window by going Tools > References... and then scrolling down to Microsoft Office Outlook and check marking it.

Expand|Select|Wrap|Line Numbers
  1. Dim olApp As New Outlook.Application, olMail As Outlook.MailItem
  2. Set olMail = olApp.CreateItem(olMailItem)
  4. With olMail
  5.     .To = strSendTo
  6.     .Subject = strSubject
  7.     .ReadReceiptRequested = False
  8.     .Body = strBody
  9.     .Attachments.Add strAttachFile
  11.     .Send
  12. End With
  14. Set olApp = Nothing
  15. Set olMail = Nothing

In this code, the various strings are pieces of information that I set elsewhere in the code, such as the recipient email address, the subject, etc.

Also, strAttachFile is the path to whatever you want to attach to the email. This implies that you need to have previously saved the report as an external file first, which may or may not be your situation.

Jun 29 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.