Click here for the changelog. (I moved it to the bottom of the page.)
Microsoft Office is designed to work together in an effort to help users get their work done more efficiently. To this end, Microsoft has given all the pieces of the Office suite the ability to control or be controlled by other of its Office siblings through the use of Visual Basic for Applications. (VBA) VBA is result of the merging of Visual Basic and the macro languages originally designed for the individual applications that now make up office. Finally, with Office97, all applications share a common macro language… and with that, we can use Access to store our e-mail addresses and use Outlook to send them… and we don’t have to do anything in the middle beyond writing a query, a simple routine and a macro to make it all happen.
NOTE: The Outlook Security Patch (it comes with Office 2000 SR-2, OfficeXP or as a seperate “security” download) makes this exercise just this side of useless. Since virus writers use these same techniques to send e-mail without you knowing, Microsoft instead pops up a dialog box for 3-5 seconds PER ADDRESS so the virus writer can’t take advantage of you. Of course, YOU can’t take advantage of this power any longer, since MS thinks you’re unable to protect yourself. *sigh* Perhaps one of these days I will write an article explaining how to do this using a freeware mail component…
You can get around the security model if you’re willing to spend a little money. Check out the Redemption object model. It’s more or less a rewrite of the Outlook model, but bypasses the security triggers. However, it’s $200 if you want to use it anywhere, and it does require installing a DLL on the client machine… if your IT policy doesn’t allow for that, you’re out of luck.
This code doesn’t work with Redemption — the theory does, but the actual code doesn’t. Maybe someday I’ll make a “Redemption” page, but I just don’t have the time right now. (If anyone wants to convert it, lemme know, and I’ll post your article.)
Here’s a page with some links about the security model: http://www.granite.ab.ca/access/email/outlook.htm
You can download the sample database in either Access97 (128k), Access2000 (132k) or Access2002 (164k) format. You can then just import the query and module into your own database and skip the cutting and pasting. I’m going to assume you have a basic understanding how Access works, and can make a query.
The Query
You have a table that has a list of email addresses. (If you didn’t, you’d be reading something else, I’m sure!) You want to send all the people in the list an e-mail… so we need to use a query to get the e-mail addresses we want. Open a new query and add the table that has the email addresses you want. Select the field with the email addresses and call it “EMail”. If the field that holds your email addresses is called anything other than “EMail” you can force it to be referred to as “Email” by putting “EMail:” in fromt of the field name in the query grid. You can also add any other fields you want for purposes of filtering and whatnot. We’re only concerned with the field known as “email” Once you get the query the way you like it, save it as “MyEmailAddresses”
The Body
To keep things as generic as possible, I grab the body of the e-mail from a text file. That way, you can just change the text file and not have to touch any of this code again. There are other ways to do this (like storing the body in the database, for instance) but using an external file allows us to look at how to grab text from a file; another handy skill to have. So, when you run the macro, it will ask you for the full path of the text file you just created with the body text in it.
The Macro
Macros provide easy ways to trigger code or run actions. In our case, we’ll use it to trigger some code we’re going to write. The nice thing is, we can make the macro first… tho the macro won’t work until we write the code. So, let’s make a macro.
- Click on the macro tab and create a new macro.
- In the action column, choose “RunCode”
- In the spot down below where it asks for the function name, type =SendEMail()
- Save the macro.
The Module
Now, we need to add some code to our database to let it talk to Outlook and send our mail. We do this by clicking on the module tab and adding a new module. After you cut and paste the code in, save the module. The name is irrelevant, but I try and group related things together in a module, so I recommend calling it “SendMail” or something similar. The other thing you need to do is set some references to the Outlook object model and the Scripting runtime. The Outlook Object Model is what allows Access to talk Outlook easily, and the scripting runtime allows you manipulate and read files. To set the references you need to check off a few boxes… here’s how.
- In the module, click on Tools then References
- Scroll down the list and place a checkmark next to Outlook x.0 Object Model (for Outlook 98 it is version 8.0, Outlook 2000 is version 9.0, Outlook 2002/XP is version 10.0)
- If it wasn’t already checked near the top, scroll down a little bit more and check Microsoft DAO 3.x Object Library
- Scroll down a little bit more and check Microsoft Scripting Runtime
- Click OK to close that window.
Failure to do the above will result in “User Defined Type not defined” errors. (There’s a chance that on newer systems DAO may not be installed. If that’s the case, then you’re out of luck until I can update this article with the newer ADO code. Tho the concepts are still the same, this code worn’t work. Sorry.) You can get the DAO libraries as part of the Jet 4.0 Engine. Grab the latest service pack from Microsoft.
The Explanation
The code is pretty well commented, but this is what it does in English. The code opens Outlook and opens your query. It asks you for the subject line to use and the text file to use for the body of the message. The code then creates a message, addresses it and sends it to each person in your list that made it through gauntlet known as “MyEMailAddresses.” Many people keep Outlook running all the time, so this routine will not shut it down when it’s done running unless you uncomment the line (remove the ‘) that says MyOutlook.Quit. Now, let’s get this code into the module and call it a day.
The Code
Copy and paste the following code into the Access code module:
Public Function SendEMail()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Set fso = New FileSystemObject
' First, we need to know the subject.
' We can��t very well be sending around blank messages...
Subjectline$ = InputBox$("Please enter the subject line for this mailing.", _
"We Need A Subject Line!")
' If there��s no subject, call it a day.
If Subjectline$ = "" Then
MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "E-Mail Merger"
Exit Function
End If
' Now we need to put something in our letter...
BodyFile$ = InputBox$("Please enter the filename of the body of the message.", _
"We Need A Body!")
' If there��s nothing to say, call it a day.
If BodyFile$ = "" Then
MsgBox "No body, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "I Ain��t Got No-Body!"
Exit Function
End If
' Check to make sure the file exists...
If fso.FileExists(BodyFile$) = False Then
MsgBox "The body file isn��t where you say it is. " & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "I Ain��t Got No-Body!"
Exit Function
End If
' Since we got a file, we can open it up.
Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
' and read it into a variable.
MyBodyText = MyBody.ReadAll
' and close the file.
MyBody.Close
' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application
' Set up the database and query connections
Set db = CurrentDb()
Set MailList = db.OpenRecordset("MyEmailAddresses")
' now, this is the meat and potatoes.
' this is where we loop through our list of addresses,
' adding them to e-mails and sending them.
Do Until MailList.EOF
' This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)
' This addresses it
MyMail.To = MailList("email")
'This gives it a subject
MyMail.Subject = Subjectline$
'This gives it the body
MyMail.Body = MyBodyText
'If you want to send an attachment
'uncomment the following line
'MyMail.Attachments.Add "c:myfile.txt", olByValue, 1, "My Displayname"
' To briefly describe:
' "c:myfile.txt" = the file you want to attach
'
' olByVaue = how to pass the file. olByValue attaches it, olByReference creates a shortcut.
' the shortcut only works if the file is available locally (via mapped or local drive)
'
' 1 = the position in the outlook message where to attachment goes. This is ignored by most
' other mailers, so you might want to ignore it too. Using 1 puts the attachment
' first in line.
'
' "My Displayname" = If you don��t want the attachment��s icon string to be "c:myfile.txt" you
' can use this property to change it to something useful, i.e. "4th Qtr Report"
'This sends it!
MyMail.Send
'Some people have asked how to see the e-mail
'instead of automaticially sending it.
'Uncomment the next line
'And comment the "MyMail.Send" line above this.
'MyMail.Display
'And on to the next one...
MailList.MoveNext
Loop
'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.
'MyOutlook.Quit
Set MyOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
End Function
Further Options
The sky is really the limit as to what you can do.
I’ve been asked about “personalizing” the text with data from the database a few times, so after I wrote my variation on the reply, I thought I’d add it to the page. It’s not particularly hard, but it requires some planning and special markups in the text file you’re using as the body.
If you need something as simple as a greeting line, all you have to do is change one line:
The big difference would be to change the MyMail.Body from your text file to something else that would have the “Hi Joe!” in it.
MyMail.Body = MyBodyText
becomes
MyMail.Body = "Hi " & MailList("FirstName") & "!" & vbNewLine & vbNewLine & MyBodyText
vbNewLine puts a hard return at the end of the line, so two of them give you in essence, a paragraph break before the text from your template.
To get a little more complicated, you can “tokenize” the text you want to replace.
Since the text of your e-mail is just a string, you can manipulate that string however you want. You could make what we call “tokens” in the template, and then use string manipulation functions to replace the tokens with fields from your database.
For instance, say you want the text of your e-mail to read: “Joe: Yesterday, you sold 20 widgets. You did good!”
You can’t just have a text file that says that, otherwise everyone in your database would know that Joe sold 20 widgets (Good for Joe!), and they’d have no idea about their own performance, so we need to create tokens.
So, we need to place specific words or phrases with generic ones:
[[Firstname]]: Yesterday, you sold [[NumberOfUnits]] widgets. [[GoodOrBad]]
Then, in the loop that goes thru the names, we’d need to replace the tokens with the values from the database… However, once we replaced it the first time, we’d lose our token (it was replaced the first go-round) so we need to create a new varibale to hold our custom-per-message body:
' This line will copy the "master" template into
' a variable we can mess around with
MyNewBodyText = MyBodyText
' Now we can replace tokens to our heart's content
' without worrying about corrupting the "master" template
MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))
MyNewBodyText = Replace(MyNewBodyText, "[[NumberOfUnits]]", MailList("NumberofUnits"))
that would then replace the tokens [[Firstname]] and [[NumberOfUnits]] with their values from the database.
A token can be any sequence of characters that won’t be repeated by accident. So, you could use the word “you” as a token, but every time it was encountered in the document, whether or not it was meant to, it would be replaced by the new value.
(i.e the above desired sentence would become “Yesterday 20 sold [[NumberOfUnits]] widgets. 20 did good!” — not the result you were hoping for…)
So, we create tokens that are unique — I use two open brackets, a descriptive string, and two closing brackets — that sequence of characters won’t occur anywhere else by accident, so I can be confident that I won’t accidentally create a sentence like the above.
You can also add all sorts of logic to things.
For instance, instead of everyone getting the “You did good!” you might use the token [[GoodOrBad]] and then use a logic statement like:
If MailList("NumberOfUnits") > 20 then
MyBodyText = Replace(MyNewBodyText, "[[GoodOrBad]]", "You did good!")
else
MyBodyText = Replace(MyNewBodyText, "[[GoodOrBad]]", "You stink!")
End If
… which would text of the person made a quota, and offer a value statement based on his performance.
(Of course, remember to then change your variable name when you assign the body to it —
MyMail.Body = MyNewBodyText
Conclusion
There’s so much you can do with this simple routine… I enjoy hearing from you (and getting your test messages!) and am glad we can help you out this way.
Here’s something else…
Someone asked me about tracking the e-mails sent in a table in the database. Since its on my mind, here’s what I wrote:
You would open the table, and after each e-mail, append a record:
[Near the top of the code]
Dim MyTrackingTable as Recordset
Set MyTrackingTable = Currentdb.OpenRecordset("TrackingTableName")
[later on in the code, after MyMail.Send]
MyTrackingTable.AddNew
MyTrackingTable("emailaddress") = MyMail.To
MyTrackingTable("emailsubject") = MyMail.Subject
MyTrackingTable("DateSent") = now()
MyTrackingTable.Update
[back to code]
'And on to the next one...
MailList.MoveNext
Loop
[etc.]
So, obviously, you’ll need a table with three fields in it, emailaddress, emailsubject and datesent. You can then tweak this table setup as you see fit.
More Recipients!
By moving your loop, you can add many recipients to one e-mail. However, in doing so, you can no longer use the simple .TO modifier, you need to use the RECIPIENTS collection, and add each e-mail address as their own RECIPIENT.
Happily, it’s pretty simple stuff.
So we take the code from above, and we change it a little:
' now, this is the meat and potatoes.
' this is where we loop through our list of addresses,
' adding them to e-mails and sending them.
Do Until MailList.EOF
' This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)
' This addresses it
MyMail.To = MailList("email")
[...]
'And on to the next one...
MailList.MoveNext
Loop
So, we morph this into:
' This creates the e-mail
' We need to move it BEFORE we start the loop, since
' we don't want to make a bunch of e-mails, we just want one.
Set MyMail = MyOutlook.CreateItem(olMailItem)
' now, this is the meat and potatoes.
' this is where we loop through our list of addresses,
' and we add them to the RECIPIENTS collection
Do Until MailList.EOF
' This adds the address to the list of recipients
MyMail.Recipients.Add MailList("email")
'And on to the next one...
MailList.MoveNext
Loop
' And now that we've addressed it, we can finish composing the rest of the fields.
'This gives it a subject
MyMail.Subject = Subjectline$
'This gives it the body
MyMail.Body = MyBodyText
[...]
Changelog
03-28-06: I dropped a link where you can get the DAO libraries
02-16-06: Typos stink. I made a typo in the “many people, one e-mail” message, an equals sign where there shouldn’t have been one. Apologies. It works now as advertised.
02-10-06: I am still amazed at how much traffic this page gets. Thanks! I’ve added a little bit on how to add multiple people to the e-mail, instead of multiples e-mails to one person each…
10-24-05: Apparently, I mucked up the personilzation code to the tune of using examples and not the object names we established in the code above it, you can’t just cut and paste it. I’ve since corrected it, and you should be able to cut-and-paste it now.
12-25-03: Merry Christmas! It seems the attachment code is broken in later versions of Outlook. If you change the position argument to 1 instead of -1, ot seems to work. I also made an Access 2002 version of the database… dunno why, really… Keep those cards and letters coming… but remember, I can’t rewrite this for you or offer too much help… this is meant to help you learn, not just drop it into a project (tho you could!)
05-28-02: I’m still flattered at how much e-mail I get from and about this document. I added a bit to explain how to add attachments.
11-13-01: I’m flattered at how much e-mail I get from and about this document. I edited the document a little for OfficeXP and added a caveat about newer systems without DAO (Data Access Objects). Also, added a comment in the code about displaying instead of automaticially sending the e-mail messages.
11-28-00: Edited document slightly to re-insert paragraphs about using a text file for the body of the message.
Hi,
I was checking the code in access 2007 and found out that there is a much simpler way to send out emails through
” DoCmd.SendObject “.
Which has an in-built functionality for sending forms, text, query reports in different formats.
Just a suggestion.
Regards
Arshdeep
DoCmd.SendObject has actually been around for quite awhile — maybe as far back as Access 2.0 but almost certainly since Access95.
The limitation of SendObject is it doesn’t allow you to have attachments that aren’t Access objects.
If you don’t have the need for attachments, the code can certainly be easier to manage since Access will handle creating and destroying the Outlook object.
In face, if Outlook isn’t available on your machine, DoCmd.SendObject will do it’s best to interface with your current default mailer.
On my setup, it was able to address and send (or display) a message via my Thunderbird client without incident… but no attachments.
Hello
and if i want to send different attachments to different mails? is it possible?
Thanks
Off the top of my head, if you know what the attachments are going to be you can add them to the email table and then send them.
So you might change the code to be:
MyMail.Attachments.Add MailList(“AttachmentFilePath”), olByValue, 1, MailList(“AttachmentDisplayName”)
…. which would require you to create 2 new fields in your email table, one for AttachmentFilePath which would be where the attachment is located, and then AttachmentDisplayName if you want the attachment to show up in the email with a specific name.
I am using this code in a database that resides on my pc and it works perfectly. When I moved this to another pc the code runs without producing an error, but the emails do not run through Outlook. Basically, nothing happens.
Do you have any ideas of why that may be?
Thank you!
If you change the code to do a MyMail.Display instead of MyMail.Send what happens?
Are you sure that the query is pulling records on the other machine? Perhaps the number of addresses is 0 in the query, so the code works fine, there’s just nothing to send?
Can you step thru the code?
Hi,
I got this working fine but whenever it opens outlook it comes up with a warning saying
“A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this?”
There is an option to click to allow this but I would like to bypass it of possible, any ideas?
I’m using Access and Outlook 2003.
Thanks,
Paul.
Paul,
The second paragraph points out this shortcoming and the third explains how might get around it.
I don’t currently have code available for the Redemption DLL. It’s on the to-do list, but at the moment, I have too many projects, not enough time!
You might also want to look at some of the resources listed here:
http://www.granite.ab.ca/access/email/outlook.htm
A question was asked via email: “How do I store the body of the email in the code or in the database?”
In the code, just take out the lines:
BodyFile$ = InputBox$(“Please enter the filename of the body of the message.”, _
“We Need A Body!”)
‘ If there’s nothing to say, call it a day.
If BodyFile$ = “” Then
MsgBox “No body, no message.” & vbNewLine & vbNewLine & _
“Quitting…”, vbCritical, “I Ain’t Got No-Body!”
Exit Function
End If
‘ Check to make sure the file exists…
If fso.FileExists(BodyFile$) = False Then
MsgBox “The body file isn’t where you say it is. ” & vbNewLine & vbNewLine & _
“Quitting…”, vbCritical, “I Ain’t Got No-Body!”
Exit Function
End If
‘ Since we got a file, we can open it up.
Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
‘ and read it into a variable.
MyBodyText = MyBody.ReadAll
‘ and close the file.
MyBody.Close
and replace it with:
MyBodyText = “The text I want to be the body of my email.”
To pull that from the database, you would have a table with maybe two columns in it, an ID column and the text column — and run a query against the table to return the text column.
I have been trying to work out a way to mail serveral people based on drop down choices. These choices are the process owners of a particular contract. I want to be able to select email and have the TO populated with these owners. I have tried various methods but none have worked. I think if I can break down your steps I may be able to apply it to what I am trying to do. following is the code I currently have in place:
Private Sub Email_Record_Click()
On Error GoTo Err_Email_Record_Click
‘Dim strArr
Dim PM As String ‘(Program Manager)
Dim PEM As String ‘(Program Engineering Manager)
Dim QE As String ‘(Quality Engineer)
‘Dim ME As String ‘(Manufacture Engineer)(errors, not sure why)
Dim SMA As String ‘(Supply Mission Assurance)
Dim Inspection As String ‘(Inspector)
Dim SOR As String ‘(Sell Off Room)
Dim SCPM As String ‘(Supply Chain Program Manager)
Dim Contracts As String ‘(Contracts)
‘strArr = EmailTo
Dim stDocName As String
stDocName = “QPIReport”
DoCmd.SendObject acReport, stDocName, acFormatPDF, (EmailTo(“”, “[email protected]”, “”, “[email protected]”, “”, “[email protected]”, “”, “[email protected]”, “”, “[email protected]”, “”, “[email protected]”, “”, “[email protected]”, “”, “[email protected]”, “”, “[email protected]”)), , , “New/Updated QPI”, “Please review attached file. Respond to me if there is any descrepencies.”, True
Exit_Email_Record_Click:
Exit Sub
Err_Email_Record_Click:
MsgBox Err.Description
Resume Exit_Email_Record_Click
End Sub
could you help me? thank you
Hi Jeff,
I am trying to setup a solution in access 2007 or 2010 where by the user clicks on a button on an invoicing form, the invoice gets printed as a PDF to a local folder (can do this part just fine), then a new email is opened (using an existing html or .oft template) and specific data is sent from access into placeholders in the template email (e.g. cust a/c, invoice number, due date), then the invoice pdf would get attached.
I have tried modifying your code form this post although cannot get it to work at all.
Could you possibly give me an idea of how I can adapt the code in your post vba into something that would work for me, as per my info above.
Instead of trying to build a template in Outlook, do as the article suggests, use a base template with placeholders then do a replace on the placeholders.
So, have your HTML file be your base template, then use tokens of some sort to replace with data.
Attaching a file is covered in the article as well.
It should work for you.
Hi Jeff,
i would like to set it so that you can choose just 1 email address from the list of email address, i guess that can be done by applying this criteria to the query [enter name:] is that correct and also i would like to open the email for editing and send maually. Is that possible.
Thanks
Matt
In response to PaulD:
I find that in order to bypass the Security Prompts in Outlook I use a program called “Click Yes”. This has allowed me to automate the sending of emails with attachments using the Docmd.SendObject.
“ClickYes” does work great, but it still does force a delay, even if you don’t have to be there to, er, click “yes” 🙂
@Matt – you can get as creative as you like with the code.
You could create a form that asks for an email address and the body of the message and then just define the variables (MyBodyText, etc.) with the data from that form instead of a recordset or external file.
Dale, you’ll note my code doesn’t use the DoCmd.SendObject syntax due to various limiations. However, it looks like your syntax for the SendObject command is wrong.
Check here: http://www.blueclaw-db.com/docmd_sendobject_example.htm
The SendObject command in the TO and CC fields wants a semi-colon separated list of addresses, I’m not quite sure what you’re giving it with that array…
I’m trying to send out an e-mail from Access where I display certain fields for a receiptient to reference and then have the receipient update only two fields in the e-mail that should then update the record when they reply back to me. The e-mail will only allow me to add records but not update existing ones. What am I doing wrong?
with your code i get the following error:
user-defined code not defined. ( with ‘MyOutlook As Outlook.Application’ highlighted. please help
Recheck your steps outlined St the beginning of the article regarding setting references.
You’re going to need a program to process the emails that come back.
That is waay beyond the scope of this article.
We only cover sending, not how to deal with replies.
Is there any “get path” type function that can be used instead of just a message box asking you for the body file? I frequently mess up when I’m manually inputting the path of my txt files.
Sure, there’s a getpath function you could use, or you can just hard code the path to the file if you want… I put the input box there just as a demonstration. The options are somewhat limitless.
What path would you be looking for?
I have a master report that contains reports that go to different clients. How would I set it up to break out the individual reports to email to the relevant client? Each page of the report goes to a different client.
I have used close to 90% of your code solutions to solve my Access 2007 to Outlook 2007 mailing issues. My default email format is HTML and I do not use the external text file for my email body, since the my database dynamically builds most of my formatted email message. How can I create and send my messages in plain text, but still keep HTML as my default editor?
Hi Jeff,
I don’t know VBA, i’m trying to learn, your explanations are a great help. Your code works great. But now I want to personalize the body.
I’m having proplems with placing the tokens and replacements. Where do I put all the code and in the right order?!
Do i need a Dim MyNewBodyText?
MyMail.Body = “Hi ” & MailList(“FirstName”) & “!” & vbNewLine & vbNewLine & MyBodyText
MyNewBodyText = Replace(MyNewBodyText, “[[FirstName]]”, MailList(“FirstName”))
MyNewBodyText = MyBodyText
MyMail.Body = MyNewBodyText
Hi Jeff,
I can’t thank you enough for this tutorial – it has been a life saver. However in my database there may be a situation where a record does not have an email address attached to it. The code above all works fine but once Outlook is open the database gives me an error – invalid use of null. I know that this is because one of the records has no email address but do you know how to get around it???
Thanks so much
How do you hard code the path to the file name? Im new to VB and I’m not sure how to do it.
That’s far beyond the scope of the article. Sorry.
I suppose you could just reset your editor setting at the end of the routine? I don’t use HTML mail, so I’m not sure.
Easiest: Rewrite your query to not return records with no email addresses.
Instead of using the inputbox to ask the user for a filename, just delete everything to the right of the = sign (from inputbox on) and replace it with your file path/name.
Can you post the sample databases please? I get error 404 when I click each one.
The links have been fixed.
Hi Jeff,
Thank you so much for the code. I’m trying to follow your codes and I’m getting “Run-time error ‘3265’ item not found in this collection” at MyMail.To = MailList(“email”). What am I doing wrong? There’s a column email in the table where the query is sourced.
Thank you,
Ron
Are you sure the column is in the query? 3265 generally means it can’t find the column referred. Is it “E-Mail” vs “Email” or something subtle like that?
i need a macro which send different attachments to different email addresses. cain it be possible.
I have now been able to create a full VBA module to send email from Access to Outlook. I have one last problem, however. I need to underline and bold one phrase “Marketing” but cannot for the life of me find out how to do it. Here is my section of code
MyMail.Body = MailList(“OpeningSalutation”) & ” ” & MailList(“Contact1 FirstName”) _
& “,” & vbNewLine & vbNewLine & MailList(“Paragraph1”) & vbNewLine & vbNewLine & MailList(“Paragraph2”) _
& vbNewLine & vbNewLine & MailList(“Paragraph3”) & vbNewLine & vbNewLine & MailList(“Paragraph4”) _
& vbNewLine & vbNewLine & MailList(“Paragraph5”) & vbNewLine & vbNewLine & MailList(“Marketing”) _
& vbNewLine & vbNewLine & MailList(“Weblink”) & vbNewLine & vbNewLine & MailList(“ClosingSalutation”) _
& vbNewLine & vbNewLine & MailList(“IndName”) & vbNewLine & vbNewLine & MailList(“Name”) _
& vbNewLine & vbNewLine & “T: ” & MailList(“Telephone”) & “/M: ” & MailList(“Mobile”) _
& vbNewLine & vbNewLine & MailList(“Address1”) & “, ” & MailList(“Address2”) & “, ” & MailList(“Address3”) _
& “, ” & MailList(“Post Code”) & vbNewLine & vbNewLine & MailList(“Website1”)
Any Suggestions
It can be done. Anything’s possible. 🙂
Unfortunately, it’s beyond the scope of this article.
Hi Jeff,
Thanks for the code, what a life saver! I have used it to automate the sending of email messages to students on courses at my local education centre. I am new to VBA but have made a number of small changes to you original code, I have removed the link to an external file for the Body Text but kept the Input Box. Unfortunately, my Body Text will not exceed 255 characters. I had hoped to copy and past paragraphs into this Input Box from a single Word file with all the different replies as applicable to students, these paragraphs can be quite long. What am I doing wrong?
An input box’s input is limited to 255 characters, so you’ll need a different way (like an external file?) to get the longer text in there.
I just wanted to let you know that I found this information very helpful, and I appreciate the work you have done. Maybe someone else will find this useful, but I modified the SQL in the query slightly to pull in unique email addresses, and to prevent duplicate emails being sent to the same person.
Here was the SQL for the “MyEmailAddresses” query:
SELECT distinct [LPS Processor Table].Email
FROM [Fc Doc Delays Table] INNER JOIN [LPS Processor Table] ON [Fc Doc Delays Table].FcProcessorId = [LPS Processor Table].[Fc Processor ID]
WHERE ((([Fc Doc Delays Table].[Date Added])=Date())) OR ((([Fc Doc Delays Table].Completed)=0));
Yes, distinct is the way to go if you just using the MyEMailAddresses for getting a list of emails with no dupes. Nice catch!
Jeff,
I need to send out an automated mail campaign on behalf of company sales people with their individual email addresses in the Outlook FROM field so that the recipients respond to their local sales reps. Do you know if this is possible?
After the .display command, is there a way to query to see if the user actually sent the email versus if he just looked at it and cancelled out of it?
I do not think so, no. You’d need to use the .send command to ensure it was sent.
Not possible… at least not with Outlook up to 2003; or at the most not without that person being a delegate for the other people .
You’d need to use a third-party SMTP component. I love jMail from Dimac Software. They have a free component which will do exactly what you want with far less fuss than getting Outlook involved.
http://www.codewanker.com/codearticle40.htm is an excellent 12 line example how it works.
When I run the Macro I always get ‘the expression you entered has a function name that Membership cant find. Moving on I find I have error 2950, argument =SendEMail(), which is the function in the Module. What am I not doing?
My first question is what is “Membership” that the error refers to?
It sounds like that’s a routine you programmed? Make sure that the SendEMail routine is either in the same module or marked as PUBLIC for it to be accessible from other modules.
Hello Jeff,
Thank you for the code. If I get over this hump, it will do exactly what I need. My problem is with the tokens and the email. When the program runs, it’s mixing the values by adding the values of the first record to the second record.
line 1 email1 name1 po1
line 2 email2 name2 po2
On the emails…for the first line the email only has the address, email1 and the body is blank.
For the second line, the email has email 2 but the data for line 1…Mr name1 your po1 has been approved.
Thanks for your help,
Angel
Make sure the bodytext token replacement code is INSIDE your loop.
If it’s before the loop, the first tokens never get populated, and then each iteration through the loop is off by the one preceding it.
So make sure the REPLACE statements go right above “MyMail.Body = MyBodyText” in the sample code. (And if you’re using all the sample code, make sure that line is changed to “MyMail.Body = MyNewBodyText”
Yessss!!!! Great!!!! Thank you, thank you, thank you for sharing…This will help me automate a couple of processes that our Inventory Software lacks.
Thanks again…
Angel
Jeff,
I used your code which is exactly what I was looking for for a while now. Instead of saying MyMail.Body = sBody (where sBody is a string passed from different routine), I say MyMail.HTMLBody = sBody so that I am sure that email opens up in HTML format. My only problem is and I am not sure if possible to do: can I have hyperlink in the body and have it automatically Underline as HTML does? currently, when I have hyperlink, it just displays as regular text. I would then have to hit space bar right after that URL for instance to have it automatically underline (to become a link). Is there a way to do it programatically via the code that creates email and email body?
Thanks for any input you can provide!
JG
Hi Jeff: Working with your code right now. Any suggestions on how to set this up so it is a button on a switchboard? Either way thanks for putting this on line.
Jeff,
I tried your code to add different attachments to different email accounts and i keep getting the following error:
“Operation is not supported for this type of object”
My table has the file path as a field and when i debug the code:
MyMail.Attachments.Add MailList(“AttachmentFilePathâ€), olByValue, 1, MailList(“AttachmentDisplayNameâ€)
AttachmentFilePath is formatted as text. This is very close to what I’m looking to do with the exception of this issue. Any suggestions would be appreciated
Funntime
Hi Jeff, I am trying to use your code to send emails, but first I want to run a report for each person and attach it as a PDF to each email that is sent, do you have any suggestions?
Thanks
Is there a way to select an given email account to send from? I am using Outlook 2007 …
I am not aware of one…
I don’t have any specific suggestions.
As your code is looping through, you’d want to call the routine to print the report, but I am not sure how to be honest. Perhaps the PDF plug-in as some sort of mode to help you control its behavior, not sure.
But then ,assuming you get the PDF to print, you would just attach it like any other attachment.
Create a macro to launch the code, and then button’s properties, just set it to launch the macro.
As long as it is a fully qualified filename (not just a path name) it should work…
I am not 100% sure the HTMLBody actually works. I haven’t had occasion to use it, to be honest. I’m old fashioned — e-mail should be plain ASCII text. 🙂
That said, make sure the hyperlink is wrapped with the appropriate HTML code; that way it should render as an underlined hyperlink when displayed.
Glad it helped!
Hi Jeff: I seem to be close to getting your code working but for some reason I keep getting the error: ‘user-defined type not defined’. Any suggestions as to what I’ve done wrong?
Is there any other error message or does the code stop on any particular line when that error is thrown?
Usually, it means you don’t have one of the references to the objects; or you edited the code and there’s a typo somewhere…
I have a Access 2007 application that needs to be modified to be able to send email with attachments. The developer that wrote the Access application is no longer here. I am very much a novice with Access and only know a little about vba. I am looking for all the sample code needed to be able to send email. We do use Outlook here. Any help would be greated appreciated.
The code that’s in the article is the code we have.
We can help you with your contract programming needs, if you’d like.
Hi, I have used the code to create a button to send emails to people, that works great, however, the body of the text desplays Ãà à ¡± á when it should be saying , this is a tester! Can you help?
Also, how can I add in to add an attachment to the email?
Tahnks,
Craig
Great stuff and thank you for posting! I was wondering if it were posible to set up the code to create an email based on a form that only lists specific records. I have a form that pulls in a product from a product list table, then a subform allows us to select multiple suppliers from a supplier table and enter the current price of the product from each supplier. What we would like to do is create a button that when clicked, would generate an email, attach a file, and the email would have all the suppliers BCC’d that are listed for that one product, to request current pricing. Is this possible? IF so, any help on how to code it would be GREATLY appreciated. TIA
I am trying set up your example in my database to be able to send email. When I try to set the Reference “Microsoft Scripting Runtime” I get an error “Can’t perform the requested operation”.
Hi, I have tried changing this to include the Dear Name by adding the following line after MyBodyText = MyBody.ReadAll
MyNewBodyText = “Dear ” & MailList(“FirstName”) & “!” & vbNewLine & vbNewLine & MyBodyText
then changing MyMail.Body = MyNewBodyText
but I get an error on the MyNewBodyText line. object variable or with block variable not set
I have added the FirstName field to the query. I am using Access 2003. Have I missed something?
Thanks…
Sorry, no help required I’ve sorted it now thanks!
Hi Jeff,
When i run the macro i get an error message saying
“The expression you entered has a function name that microsoft access cant find”
Could you help me withthis please?
Make sure the function name is typed the same as the name in the macro you’re trying to run. A typo would create that error.
For instance, if you said =Sende-mail() or =SendMail() it’d throw an error…
Hi Jeff thanks for that just one more question where do i store the text file because when i fill out the subject it asks me for the file name of the body? Also do i save it as a word doc and where?
Karl – it is a text file (*.txt) not a word doc, you create and save it in notepad with your chosen name. Store it anywhere you like eg same location as your database if you want, but you have to enter the full path for the location so it can find it. eg BodyFile$ = “C:\TEST\Body.txt”
Sorry my mistake. It’s working now. Got to say this is an excellentpiece of coding thanks for passing it on!
Question, is there a way to send the emails to a folder besides the Outbox, a Draft folder for example to view before actually sending them?
You could use .Display instead of send, or if memory serves, there’s a .save you could use also, which I believe puts a copy in the drafts folder…
Hi Jeff
Thanks for this tutorial, it comes very handy.
However there’s something I have tried for so long, but I was unable to come up with an idea, hope you can help me.
In my job everyone has a email account provided by our customer, moreover, we have a generic email account which all of us have access to it. We have to send notifications using that account, but as we have Microsoft Outlook with our own email account, we have to close Outlook and open it again using the generic email.
Is there a way we can change the email sender using your VBA code? This to avoid have to close and open Outlook everytime we have to send email notifications from Access.
I appreciate in advance any help you can give me.
Thanks!
If you need to send from another address, you need to make sure Outlook is running under the profile you need.
Otherwise, I’d say this solution isn’t best for you, and I’d look at something that uses an SMTP component (as opposed to MAPI) like JMail to send from different addresses.
Jeff:
Thanks for your great page. We are running an Access script via the Windows Scheduler, with a ‘Run As’ userid. The Scheduler task can be run when the user is NOT logged in to Windows.
Is there any way to have this (or SendObject) work without the user logged in? Is having an existing Mail account set up for the Run As user enough?
Thanks again!
I’m working on a project for an automotive specialist. Here’s the problem I have: the form I have set up for retrieving individual records for customers I want to automatically send an invoice to that individual customer via email. I don’t have much experience in VBA coding so I was wondering how I can do this. The form has a non-tabled text field for the invoice area (the body of the email) and I have no clue how to do this. Can you help me out on this one?
It’ll go something like this. Adapting the code from above, you can copy and paste it behind a button on the form and then customize it to the form. So you don’t need to muck about with queries, since the fields will be on the form itself.
Sub btnSendMail_Click()
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
‘ You need to assign a subject
Subjectline$ = “Invoice from Drew’s Cars”
‘ You need to define the body of your mail:
‘ this part you will need to customize yourself. I can’t make guesses as to your needs.
MyBodyText = “Thanks for using Drew’s Cars. You owe us ” & frmMyForm(“Total”)
‘ Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application
‘create the mail
Set MyMail = MyOutlook.CreateItem(olMailItem)
‘ This addresses it
MyMail.To = frmMyForm(“ClientEmailAddress”)
‘This gives it a subject
MyMail.Subject = Subjectline$
‘This gives it the body
MyMail.Body = MyBodyText
‘If you want to send an attachment
‘uncomment the following line
‘MyMail.Attachments.Add “c:myfile.txt”, olByValue, 1, “My Displayname”
‘ To briefly describe:
‘ “c:myfile.txt” = the file you want to attach
‘
‘ olByVaue = how to pass the file. olByValue attaches it, olByReference creates a shortcut.
‘ the shortcut only works if the file is available locally (via mapped or local drive)
‘
‘ 1 = the position in the outlook message where to attachment goes. This is ignored by most
‘ other mailers, so you might want to ignore it too. Using 1 puts the attachment
‘ first in line.
‘
‘ “My Displayname” = If you don��t want the attachment��s icon string to be “c:myfile.txt” you
‘ can use this property to change it to something useful, i.e. “4th Qtr Report”
‘This sends it!
MyMail.Send
‘Some people have asked how to see the e-mail
‘instead of automaticially sending it.
‘Uncomment the next line
‘And comment the “MyMail.Send” line above this.
‘MyMail.Display
‘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.
‘MyOutlook.Quit
Set MyOutlook = Nothing
End Sub
Hi Jeff,
I have everything working great. However, I wanted to ask if its possible to have the “BodyFile$” look for a .doc or .docx rather than a .txt file?
I want to add a logo to the top and bottom of the email.
Please let me know.
Thank you,
Allan
Graphics are a touchy thing when trying to send with Outlook this way.
I’m afraid you’re going to be in for some trial and error.
Hi,
I have a problem, I want to be able to sort which users get sent mail depending if their account status is expiring. Instead of send mail to everyone in the table.
Before I put the automatic e-mailing, I put
>Now() And <[Input End Date]
In the criteria box under the account status.
When I use this in conjunction with the e-mailing the debugger will highlight
Set MailList=db.OpenRecordset("qry_Account_Status_Check")
Please help!
You have to have a SELECT query named “qry_Account_Status_Check”; it seems you’ll also need to pass parameters to it.
AMAZING, thanks for the help!
Hi to all! I just want to comment and add some info related to Response No. 4 from Mr. Knapp. Specifically, the correct syntax for using a recordset to insert the attachment paths is:
MyMail.Attachments.Add (MailList(“AttachmentFilePathâ€))
There should be another set of parenthesis to enclose the recordset field reference.
D’oh! Thanks for the catch! 🙂
You’re welcome Mr. Knapp. I would like to thank you also for providing your Send Mail code. When I was learning VBA, it helped me a great deal to learn how Access can “communicate” with Outlook. Best regards!
Thank You!!!! This was exactly what I needed. My only problem is that pesky security alert box that pops up.
Maybe one of these days, someone can find a free alternative. I do appreciate your examples you provided. Well done.
Hi
Thanks for this tutorial – it’s been a great help!!
Could you assist further? I need to attach a report to the email. Your assistance would be greatly appreciated.
Hi I wonder if you can help me further.
I have implemented your code to send emails and it works great. When I use “MyMail.Recipients.Add MailList(“email”)” it gives me a single email to many recipients addressed as To: I would like to send as BCC: rather than To: to prevent all email addresses being visible to recipients of the email.
I have used MyMail.BCC = MailList(“email”) which sends a blind copy but only to one person rather than all email addresses in my query. I haven’t worked out how to get multiple address into BCC.
Buried in the comments there should be some code about moving the loop.
Basically, you want the loop thru the addresses to surround the BCC field, and keep adding people to the BCC field.
I am trying to create a Package logging system in one of the residence halls at the University of Michigan. A user will import the information about the package and a student. We have the entire list of students in the building in both a table and a query (as suggested above). Now, I have altered the code you provided to have a set subject and a set body, but I would like to select one user from the query and use the email from that selection. Is there a way to do this easily? Please let me know, I would greatly appreciate it.
Thanks!
You could either set a parameter of the query to accept something from the form that would return one record; or just ignore the query altogether, get rid of the loop and just pass into the code the Email Address.
I’ve been using this terrific code for years, and now it suddenly stopped working on one of the cumputers. (I suspect an Office update to be the cause.) I use Office 2003. I receive the following error: “Automation error – The specified module can not be found”. The debugger then highlights this line in the code: “Set MyOutlook = New Outlook.Application”. All the references are in place. Help greatly appreciated.
Never mind the previous, I found a solution. I had to change the following lines of my code:
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Set MyOutlook = New Outlook.Application
to this (late binding instead early binding):
Dim MyOutlook As Object
Dim MyMail As Object
Set MyOutlook = CreateObject(“Outlook.Application”, “localhost”)
I don’t know if it was essential to change to late binding, but the key to solving the problem was adding “localhost” to the Set MyOutlook line.
Hopefully, this can help others with the same problem. You won’t believe how long I’ve been searching for a solution to this! And also, it looks like the problem is caused by an update to Outlook (2003).
Hi,
I am new to Outlook and hig level functionality within Access.
I have two tables:
Table 1 has with rows of data for multiple persons (Name, …, …, ….)
Table 2 has the email details (Name, Email Address, cc, Greetings)
I want to be able to send an email to each person with an attachment from Table 1 with only the rows related to that person.
Help please.
Hello.
Thank you for the post. It was very helpfull.
Everything works fine if the ms outlook is open, however if outlook is close it returns the following error:
“Run-time error ‘287’: Application-defined or object-defined error”.
How can I open ms outlook in the code if it is closed?
Thank you.
First, let me just say…for a technically inclined person, this post, in plain English is wonderful! I could follow how to do everything I needed to! I’m excited about the possibilities of this…but I’m not able to get it running. I think I am missing a step that most users are already aware of. I have created all of the steps required, but I have no clue how to actually get the module/macro/email to work. I click on the query, it opens. I click on the module, it opens. I click on the macros and I get an error message. Can anyone tell me what I am missing here?
Tough to say what you’re missing. Perhaps the function is defined as PRIVATE and not PUBLIC that you’re trying to launch from the macro?
What’s the error message say exactly?
I did as the code below suggest to send different attachments to different receipients. I added the two additional fields as well but keep getting a run-time Error 3251 – Operation is not supported for this type of object. I am able to attached a generic file using the code if I specifiy in the VBA code. Also, when I try to change the name to a more meaning for name I keep getting the file name. Any ideas?
MyMail.Attachments.Add MailList(“AttachmentFilePathâ€), olByValue, 1, MailList(“AttachmentDisplayNameâ€)
…. which would require you to create 2 new fields in your email table, one for AttachmentFilePath which would be where the attachment is located, and then AttachmentDisplayName if you want the attachment to show up in the email with a specific name.
Good post but I would like to add that you need to setup the tools>reference for the Microsoft Outlook Library from within the Module window.
I’m not as technically advanced and started using Access 1 week ago but have learned a lot (designed my forms, tables, made relationships and built the switchboard.) I started with the Contact Management template that MS Access provides and have all my records in there because I did not want this particular group of contacts in my Outlook.
All I want to do is send one, manually generated email at a time, like I do in Outlook. In Outlook I would click on the New Message to Contact icon when in the contact record.
Is there a way to send an email thru Access other than cutting and pasting the email address into a blank email in Outlook? I CAN do that, since I’m only sending an email every 1/2 hour or so and not even all day. But I just wanted to know if there was something I could do in Access that would open Outlook and make a new email message open. I am handwriting the email anyway cause they are all individual. Thanks! (you might have broken a record here for longest thread time evah!)
I believe right on the form if you drop a button, you can tell it to make a “send email” button.
Otherwise, the code in the article can be simplified to just open a message and populate the TO field.
I’m new using Access 2010 and have never used the macro functions (until today…) or done any coding. I would like to use EmailDataBaseObject command to open an email and populate the BCc field from the email field of a query. My only problem is that I don’t know the correct syntax to use within the Expression Builder to do this.
Is this possible or will I have to follow your instructions above as you have mentioned to Nancy?
Thanks!
Please help i need to link dates in my database to my calender ie,
I have a database set up that gives me client information and this includes the dates in which they took out a contract with us from this i enter dates that tell us when we need to contact the customer, send off for a bill analysis and review the bill with the customer. I need these dates to go onto my calender and the sales reps calender looking after that clients calender.
Also a code that would see dates automated once a contract start date is entered as i am manually entering these. Thank you
Great Code! Have a suggestion though
Your code starts a new instance of Outlook. If outlook is already open you end up with a second instance of Outlook and a longer time to load your e-mails. You also end up with hidden instances of Outlook if you do not uncomment MyOutlook.Quit
Accordingly,
Why not Make MyOutlook an Object
Dim MyOutlook As Object
Replace your code Set MyOutlook = Outlook.Application with
On Error Resume Next
Set MyOutlook = GetObject (, “Outlook.Applicationâ€)
If Err.Number = 429 Then
‘ GetObject failed Outlook is not running, Then statement launches new instance of Outlook
Set MyOutlook = CreateObject (“Outlook.Application”)
End If
My suggestion is this. Most users have Outlook open all the time–hey its your e-mail. Using an already running Outlook means a faster loads for users at runtime. It also avoids the issue of hidden instances of Outlook sucking resources.
Just a suggestion
Tim
Attorney and Hardcore Coder.
This is a wonderful tutorial and I feel like I am so close to getting this worked to fit my needs, but I am getting stuck.
I am using a Form combobox to feed a parameter on a query to get my desired list of emails. I built the query manually in Access and it works fine, but running it through your module to get the email to populate correctly is proving problematic.
I was getting error 3061, that parameters were expected, and searching revealed that I likely had an issue with having parameters set in the manually built query interfering somehow with the code. Building the query in the module with code was supposed to solve the problem.
Here are my variables:
————————
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim strEvent As String
Dim strEmail As String
Set fso = New FileSystemObject
strEvent = ” & [Forms]![CivicLeaderAll]![cboEvent] & ”
strEmail = “SELECT CivicLeaders.email AS EMail ” & vbCrLf & _
“FROM CivicLeaders ” & vbCrLf & _
“WHERE (((CivicLeaders.email) Is Not Null) AND ((IIf([EventType]=’strEvent’ Or [Event2]=’strEvent’,’strEvent’,Null))=’strEvent’));”
———————————
Then I set up the database like so:
———————————
Set db = CurrentDb()
Set MailList = db.OpenRecordset(strEmail)
———————————
Then I use your multiple recipients option like so:
———————————
Set MyMail = MyOutlook.CreateItem(olMailItem)
Do Until MailList.EOF
MyMail.Recipients.Add MailList(“EMail”)
MailList.MoveNext
Loop
MyMail.Subject = Subjectline$
MyMail.Display
Set MyMail = Nothing
Set MyOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
—————————-
The result is it opens of an instance of the intended email with no recipients in the TO: field. Any suggestions?
Thank you for your help!
-Theo
First off, the where clause of your SQL statement is confusing and doesn’t seem right, so I”m not sure what you’re trying to accomplish. What are you testing the result of the IIF statement against?
So, I assume that’s why you’re getting a 3061 error.
But if we assume the SQL is right, then we need to ask:
Is “CivicLeaders” in your SQL statement a table or a query? If its a query, get rid of any parameters in it, since you seem to be setting them in the SQL statement. Or, you just need to set up the recordset as an object off of a QueryDef object, and set the parameters that way.
Thanks man…your code rocks! But my senerio is a little tricky. Here’s what I’m trying to accomplish: I need to be able to send 1 invoice per each customer that I have from a table. On each invoice there could be 2 or more months worth of charges. In other words, there are could be multiple records associated with the 1 invoice. The customer field in my table does not have unique values.
As the script is currently written, 1 customer will receive 1 invoice per charge instead of multiple charges being displayed on 1 invoice.
Is there a way to assocate 1 invoice per customer that has multipe rows of charges in the table?
I have a issue where i have to send the email to a particular person when Some date is reached.
E.g: the account password expires on say “20/11/2011”
then two days before the date the persone should get a email notification of the same.
I am new to access, would appreciate some help on this.
You will need to run the program every time you want to send emails. Have your query pull the info you need based on dates or whatever your criteria is, and then manually kick it off. Otherwise, you’re going to have to write code to watch the clock and fire itself off at given intervals.
That code is beyond the scope of this article.
Dear Jeff,
Wondering if you’re kind enough to help with the tokenize/loop issue, spent hours to solve, please I need your help! The codes produce emails for everyone on my Query with the same record set – the first one from the Query. I just wanted to tokenize EmployerName, EmployerNo and Forenames in the body of my email (using a text file) by pulling up the record set that corresponds to the email address on my Query.
Hope this makes sense.
Thank you so much for your help in advance.
Public Function EmailEmployerAxisNumber()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Set fso = New FileSystemObject
Subjectline$ = “Pension contribution returns online form EAS5”
BodyFile$ = “P:\DFS\Pensions\AccessLinked\EmployerAxisNumber.txt”
Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
MyBodyText = MyBody.ReadAll
MyBody.Close
Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
Set MailList = db.OpenRecordset(“EmployerAxisNumberTest”)
Do Until MailList.EOF
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.To = MailList(“Email”)
MyMail.Subject = Subjectline$
MyMail.Body = MyBodyText
MyBodyText = Replace(MyBodyText, “[[EmployerName]]”, MailList(“EmployerName”))
MyBodyText = Replace(MyBodyText, “[[EmployerNo]]”, MailList(“EmployerNo”))
MyBodyText = Replace(MyBodyText, “[[Forenames]]”, MailList(“Forenames”))
MyMail.Send
MailList.MoveNext
Loop
Set MyMail = Nothing
Set MyOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
End Function
OK, two things.
1. You need to move your MyMail.Body = MyBodyText to AFTER you do the token replacements
2. You need to RELOAD your Body after you make the first one.
To illustrate:
The first time thru the loop your MyBodyText is “Firm [[EmployerName]] hired Employee #[[EmployerNo]] …”
But once you do the token replacements, the variable is now “Firm Jephens hired Employee #1…” so the next time thru the loop, there are no tokens to replace.
So, above the loop, add this line:
MyTokens = MyBodyText
Then as the first line in the loop (Right after “Do Until MailList.EOF”), re-initalize the MyBodyText variable:
MyBodyText = MyTokens
And that should take care of it.
Jeff
Thank you Jeff so much for your prompt reply – really appreciated.
I’m going mad! After making the changes you suggested, I can only send one email – and can’t get the loop working. I was afraid to ask you once more if you could kindly tell me where I was getting it wrong.
Public Function EmailEmployerAxisNumber()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim MyTokens As String
Set fso = New FileSystemObject
Subjectline$ = “Pension contribution returns online form EAS5”
BodyFile$ = “P:\DFS\Pensions\AccessLinked\EmployerAxisNumber.txt”
Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
MyBodyText = MyBody.ReadAll
MyBody.Close
Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
Set MailList = db.OpenRecordset(“EmployerAxisNumberTest”)
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.To = MailList(“Email”)
MyMail.Subject = Subjectline$
MyBodyText = Replace(MyBodyText, “[[EmployerName]]”, MailList(“EmployerName”))
MyBodyText = Replace(MyBodyText, “[[EmployerNo]]”, MailList(“EmployerNo”))
MyBodyText = Replace(MyBodyText, “[[Forenames]]”, MailList(“Forenames”))
MyMail.Body = MyBodyText
MyTokens = MyBodyText
Do Until MailList.EOF
MyBodyText = MyTokens
MyMail.Send
MailList.MoveNext
Loop
Set MyMail = Nothing
Set MyOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
End Function
Great piece of code Jeff.
2 things I wanted to add if possible – I have 4 different accounts set up in my outlook, for different businesses. How can I specify which one I want it to be sent from ? And also can i somehow code the reply address that appears in the email
I haven’t looked at the programming model for Outlook 2010, so maybe something changed — but I do not believe you can choose the account you send from; it’s just going to use the default.
For things like that, I love the JMail.DLL – it’s easy to program against, they have great documentation for it, and the basic version is free.
http://www.dimac.net/
Jeff,
First, let me thank you for posting this info. It has been extremely helpful for me as a beginner user of vba and access 2007.
I wanted to see if I could get some help on an issue. My query has two email fields…”Email_Father” and “Email_Mother”. I’ve modified the original code to build a recipients list, but it errors out if either field is null. Is there a work around for this?
I’ve tried some If Then statements, but it really has a problem if both fields are null.
Thanks again for your help.
Please disregard my last posting.
I finally figured it out. Just had to add two sets of If Then’s…one set for building the email list and the other for executing the MyMail.Send command.
Ok, all of this makes sense but I am unable to use VBE. I have a query that brings up certain emails based on a ComboBox After Action response. I would like these emails to be put into Outlook so I can create an email. I appreciate any help you can provide.
All of this makes sense but I am unable to use VBE and there is no changing that. I need to make a macro that will take my query of emails and put it into Outlook so I can email the group. I would appreciate all the help I can get!
You can’t use VBE because of security or policy restrictions? I don’t think what you want to do is possible without using VBA since I do not think you can loop through a recordset from within a macro..
This is going to sound absurd but I thought I had the entire Microsoft Office for Mac but I only have Excel, Word, and PowerPoint. I was going to do it from a blank Access Database and export it from home and email it to myself from work. Can I use OpenOffice instead and do the same thing? If so, I need some help Sir. On a ComboBox that triggers a query named qryEmailList I would like to take those emails without null entries or duplicates and put it into the “To:” portion of Microsoft Outlook. What is my code?
Jeff,
Thank you soo much for posting this very helpful code. I am new to VBA and am anxious to learn enough to be able to write freely,but for now I need help.
I am using a form (frmEmailAllReports)to enter start and end dates to call a query (qryEmailAll_qryclinic_level_report). I want to automate the email process of emailing reports that calculate scores for individual managers. I am having difficulty with the “Set MailList” portion of the code.
This is what I have so far:
Option Compare Database
Option Explicit
Public Function SendEMail()
Dim fso As FileSystemObject
Dim Subjectline As String
Dim MyBodyText As String
Dim BodyFile As String
Dim MyBody As TextStream
Dim MyOutlook As Outlook.Application
Dim db As DAO.Database
Dim qdf As QueryDef
Dim strEvent As String
Dim strSQL As String
Dim txtStartDate As Date
Dim txtEndDate As Date
Dim R_Date As Date
Dim MailList As DAO.Recordset
Dim MyMail As Outlook.MailItem
Set fso = New FileSystemObject
‘ Populate Subject Line
Subjectline$ = InputBox$(“Please enter the subject line for this mailing.”, _
“Mystery Caller Survey Email All Reports!”)
‘ If there’s no subject, end Process.
If Subjectline$ = “” Then
MsgBox “No subject line, no message.” & vbNewLine & vbNewLine & _
“Quitting…”, vbCritical, “E-Mail Merger”
Exit Function
End If
‘ Populate Body Text
BodyFile$ = “G:\My UCLA Items\Mystery Caller\MystCallEmailScript.txt”
‘ If there’s nothing to say, end process.
If BodyFile$ = “” Then
MsgBox “No body, no message.” & vbNewLine & vbNewLine & _
“Quitting…”, vbCritical, “I Ain’t Got No-Body!”
Exit Function
End If
‘ Check to make sure the file exists…
If fso.FileExists(BodyFile$) = False Then
MsgBox “The body file isn’t where you say it is. ” & vbNewLine & vbNewLine & _
“Quitting…”, vbCritical, “I Ain’t Got No-Body!”
Exit Function
End If
‘ Since we got a file, we can open it up.
Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
‘ and read it into a variable.
MyBodyText = MyBody.ReadAll
‘ and close the file.
MyBody.Close
‘ Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application
‘Set your Query
Set MailList = db.OpenRecordset(“strSQL”)
strEvent = ” & [Forms]![frmEmailAllResults]![txtStartDate] & [Forms]![frmEmailAllResults]![txtStartDate] & ”
strSQL = “SELECT Results.Clinic_Name, Contacts1.Contact_First, Contacts1.Contact_Last, Contacts1.EmailAddress FROM Results INNER JOIN Contacts1 ON Results.Clinic_Name = Contacts1.Clinic_Name WHERE (((Results.Clinic_Name)=[Form].[frmEmailAllReports].[Clinic_Name]) AND ((Results.Date_of_call) Between [Form].[frmEmailAllReports].[(txtStartDate= ’30/12/1899′)] And [Form].[frmEmailAllReports].[EndDate ’30/12/1899’]));”
‘ Set up the database and query connections
Set db = CurrentDb()
qdf.Parameters = strSQL
‘ Loop through the list of addresses,
‘ adding them to e-mails and sending them.
Do Until MailList.EOF
‘ This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)
‘ This addresses it
MyMail.To = MailList(“email”)
‘This gives it a subject
MyMail.Subject = Subjectline$
‘This gives it the body
MyMail.Body = MyBodyText
……….
Any guidance or advice would be greatly appreciated!!
This is great!
Within your “Module” instructions, when I check all three boxes and hit ok I get this message:
“Name conflicts with existing module, project, or object library.”
I have a database made in Access. Whenever a user finishes and saves, I want an option that sends a notification email to others preferably in xlsx, txt, or pdf.
Would I be able to do this from setting up a macro for a button, in addition to the coding you have provided?
In addition, I am using Outlook 2010 and Access 2010.
I have code similar to above, when I try to run it I get a Compile error:
Sub or Function not defined
and it highlighting the Replace function, I know that I have to call the function but when I try it give me a different message of can find End Function
Please help.
Thanks
Hi:
Thanks for your tutorial, it’s excellent. Is it possible to use a .html “filename” in lieu of a .txt and get it to diplay as a webpage within the sent email?
Jeff,
Wondering if you can help. I have database that produces a report with 120 pages, each page contains different information indexed to ‘POINTREF’. I am trying to write a code that will automatically convert the pages to individual pdf’s, name the pdf’s according to the ‘POINTREF’ field for each page and then email the pages to the email address of a linked table which contains the contact information. Is this possible?? Not sure where to start.
Not as far as I know. You *should* be able to the HTMLBody attribute in place of the Body attribute; but I do not think it works. I may be wrong, I haven’t tried it in anything recent.
Without knowing what other modules you have loaded, I can’t offer much assistance.
Hi Jeff,
Thanks for the script it works brilliantly!
Jeff.
My goal is to send an email notification to multiple recipients. I used a query for the list of emails and for the body I would like to add texts from a form. I read the 84th post and do not fully understand how to make it work.
-The form has multiple fields, name/date/summary/etc.
example of the email:
To: Recipients (working)
Subject: text (working)
Body: Hello, We have a new update on the database! http://www.asdf.com/database
Then I want the whole form in Text.
Do I need a new Dim? In addition, how do I exactly configure the button mentioned in post 84?
——
Public Function SendEMail()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Set fso = New FileSystemObject
Subjectline$ = “Issue Database Update Notification”
MyBodyText = “Hello We have a new update on the Database! http://www.asdf.com/database”
Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
Set MailList = db.OpenRecordset(“EMail”)
Set MyMail = MyOutlook.CreateItem(olMailItem)
Do Until MailList.EOF
MyMail.Recipients.Add MailList(“Email”)
MailList.MoveNext
Loop
MyMail.Subject = Subjectline$
MyMail.Body = MyBodyText
MyMail.Display
Set MyMail = Nothing
Set MyOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
End Function
I am using a similar code, works great, but am puzzled on how to add an attachement. I am looking to simply add an Access report, as a PDF, as an attachement. I don’t want to print, save to local drive, then add path to attach it, but rather as an access object, pdf format. Any help?
Thanks
Eli
I’m not sure you can. I think you need an intermediate PDF file (saved in the temp folder) to attach. Otherwise look at DoCmd.SendObject – it might do what you need.
Excellent facility you developed – THANKS; Can I send the bulk mails through Outlook without having to click “Yes” to allow each email to be sent?
THANKS
Using this code, you have to hit YES. There are links to how to do it without having to hit YES elsewhere in the article using a tool like Redemption…
I’m hoping you might be able to help with what I hope is a simple request. I’d like access to output an excel file and attach it to a template email that have already prepared. I can get access to output to an email but I can’t find a way to get it to use an existing template (oft) any ideas?
I am trying to reformat the Task Template that is prebuilt in Access and was wondering if I could get some help.
I would like to have multiple attachments (all different attachments; some are word docs and some are Lotus forms) email to multiple email address’s.
Also, I would like it to have the Start Date, and Due Date as it has but I would also like a Review Date which can calculate 2 years if there is no revision required or 180 days if a revision is required. Can anyone help me manipulate this template to work with what I need? PLEASE!
Ok, I now have access to VBA and need some help here. I would like to have an email option on one of my forms that has a Combobox. On the selection of whatever category (ie.Managers) that opens up Outlook and places the email addresses of all the employees that are in the category “Managers”. I do not (not) want to have a template email so I want to manipulate the Subject and Main Body of email. I already have everything I need to make it happen. So, with that being said, what should I do? Thanks in advance!
Broad strokes here – you’ll need to fill in the rest —
In the code behind the button to populate the list, run a query against your list of addresses using the combobox as criteria (I’ll assume you have one table of departments with IDs and another table is employees and their departments, so you can filter easily) and then use a little of the code in the article to just populate the Addresses collection by looping through the recordset.
That’s pretty much beyond the scope of this article.
You’ll want to check out some sites that are devoted to Access development.
Something like this:
http://forums.devarticles.com/microsoft-access-development-49/
Or just type “Microsoft Access Development Forums” into Google…
Try the code snippets here:
http://www.pcreview.co.uk/forums/sending-email-access-2007-through-outlook-2007-using-template-t3953808.html
We offer a commercial program, Total Access Emailer, designed exclusively for Microsoft Access that bypasses Outlook and its limitations by using SMTP. More info here: http://www.fmsinc.com/MicrosoftAccess/Email.asp.
It runs as an Access add-in, uses a table or query as a data source, and sends custom emails to everyone in the list. It can also attach reports as PDF files and filter them so each recipient receives their own data. Advanced features to zip multiple files and add a password improves the security of attachments. A VBA interface and royalty-free runtime library is part of the professional version. A free demo is available here: http://www.fmsinc.com/MicrosoftAccess/Email/free-trial.html
Hope htis helps.
Hello Jeff,
I just read through all of the post to make sure I am not wasting your time by asking a question over again. I would like to setup a combo box before outlook opens where the neccessary email address can be selected. If possible can you assist me with this. Also, I am receiving the the error “The Expression you entered has a function name that database cant find” it is public so i am not sure what the issue is the code will run if i do it from vb but not if i run a macro in access.
I’ve used FMS’s stuff in the past and it works as advertised, no complaints, and also solves the problems some of you have commented on — so check them out
That said, a non-commercial solution I also use when I don’t need to use Outlook is the excellent free SMTP mailer from Dimac – JMail which lets you use ANY SMTP server (not just Outlook) to send mail.
http://www.dimac.net/default3.asp?M=FreeDownloads/Menu.asp&P=FreeDownloads/FreeDownloadsstart.asp
Hello
This works wonderfully – thanks! I only have more question – is there a way I can make a different kind of file the body of the email, for example a pdf file? Or am I stuck with sending it as an attachment?
Pretty much stuck running the attachment route…
Hello, Jeff!!!
This works perfectly with Outlook 2007, thanks a lot for your great support!
This worked beautiful, thank you. the only problem i had in access 2007 was how the email address is stored
ie:[email protected]#mailto:[email protected]#[email protected]
so i had to write a sub procedure during the loop.
incase someone else had this problem just ad this sub
Dim a
a = InStr(1, [EmailAddress], “#”) – 1
If a > 1 Then
DoCmd.RunCommand acCmdSelectRecord
Me.EmailAddress.value = Left(EmailAddress, a)
Else
Exit Sub
End If
a = “”
it helped me sovle the issue, again thanks for this awsome code
Hi Jeff, Thank you very much for your generosity in sharing this code and the support you have given afterwards. I started to use this code and it works fine for me. The only problem I encounter is that all my mail are returned as the To: email address is quoted with single quotation marks. eg. ‘[email protected]’. Can you help me on this one as to get it like [email protected] without the quotation marks please. Thanks again
Jeff I sorted the error. It is in my mail client.
Just to help on error 2950 of John S;
If your module name is as same as your function name then the ‘Runcode’ will not work in the macro. Just rename the module to a different name.
Thanks
You made this as difficult as possible for a novice. All i want to do is send a MS email after i get through typing it. I have not found the send button yet. I gues you think everyone is a pc expert. wonderful.
Ok, so once I get this figured out I have another issue that stems from this. I know it is outside this conversation but I can’t run this script if I can’t get my query to run. I have a form that has individuals from my company. It has their general information as well as a list of positions under the organization. Some members hold multiple positions. I have included a check box option for each individual position to allow the users the ability to check multiple positions. How do I run a query if I want all of one category or mulitple categories? I can’t figure it out. My idea was to add a list box that allows you the ability to check multiple positions and an All option but can’t figure out how I work it into the query. I was thinking something using the Like function. Take all categories selected in the list box and if they have a True in this category then show these results only. Can someone help me please?
Paul:why dont you create a query for each category to append to a general table then all the options will be in 1 table then you can do whatever you want with this data, i actually had to perform this type of scenario before.
hopefully this kinda helps
This was my original thoughts but I wanted to prevent unneccessary queries if it was possible. It’s looking more and more like I’ll have to do it that way. Thanks for the response!
well u could write your queries in code technically you wouldnt have a bunch of them like this you may have to write 3 or 4 of them but i think its your only option.
Dim strSQL As String
strSQL = “UPDATE Products SET Products.stocklevel = [partnum] WHERE (((Products.partnum)=[forms]![workorder]![part10part].[value]));”
DoCmd.RunSQL strSQL
Hi Jeff, I have a very basic question (I think) but have really been struggling to get your great code to work as it always returns the key and not the data (ie the email address). I have to use a lookup column for the email addresses as I have to filter emails to send against other data in the main table. How can I get this to work running the query against against data stored from a lookup column rather than running a query directly against the email address table? Many thanks
It’s all in how you write your query. I used a simple query of the email address table, but you can query anything. So write a query (using the Wizard or however you’re most comfortable) that links the data to the email address. There’s likely a join that will do the work for you. Then, run THAT query against the code, and it should do the trick.
I’m not sure my code is appropriate for the task you’re looking for — regardless of its technical level.
Excellent catch!
By using the Outlook Redemption Security Bypass for RDo
with this code allowed me to send my attachments embedded in the body. Not As An Attachment .Basically i created a newsletter database.
the images are stored on a web server and the body text is formatted with basic html. When the user recieves the email the link embedded into the body shows the image.i will post the link if Jeff allows It . Basically i needed jeffs code and the code from Redemption To Perform The Newsletter DB. It works Quite Well. I wanted to post up the body code to show quickly whats involved but the formatting would be truncated due to the limitations of this box im responding in. I Dont want to step on Jeff for his awesome Code i just want to also show how it enlightened me to go further and that this Access Blog is For Learning. So Lets Do Some Learning instead of being Spoon Fed. 🙂
Rand — by all means, feel free to post the link.
Well here Goes. By using Redemption RDO Objects you can attach files to the body instead of attaching them to the email. the link is: hxxp://www.dimastr.com/redemption/home.htm. Download and install the free developer version .Lets say you dont want to send huge image attachments to alot of people, you can just upload the huge image on your web server and create a link in the email htmlbody section. This saves bandwidth for everybody.
The reason you need code to perform this is beacause the html does not format properly when you type it out by hand. it has to be read by a text stream object in order to view it before sending the quickest way. Sure you could sit there and view then edit and view and edit all day long but this method is fool proof and is performed by all email marketers. You could also use the code to insert your image into the body section by changing a few variables. my code actually uses jeffs code here and calls a sub routine which creates the RDO session. The Difference is we need the Redemption developer session to bypass the security to enable all this to happen.
so in the next posts i will put up in 3 parts. 1- the redemption code, 2- the body text file formatted in html, 3- the call to the subroutine. Not all of jeffs code is int these parts but i just want you to see that its possible using outlook 2007 and 2010. Jeffs code got me started and i eventually used his code and the objects from the redemption module.
Part 1- hopefully you downloaded and installed redemption.
the actual code to generate the message.
you need all 3 parts and the edited body text with your own links in it.
Private Sub Command169_Click()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim BodyFile As String
Dim MyTrackingTable As Recordset
Set MyTrackingTable = CurrentDb.OpenRecordset(“tracking”)
Dim intI As Integer
BodyFile$ = “C:\body\bodyhtml.txt” ‘this is the preformatted html with the link to the image
If BodyFile$ = “” Then
MsgBox “No body, no message.” & vbNewLine & vbNewLine & _
“Quitting…”, vbCritical, “I Ain??t Got No-Body!”
Exit Sub
End If
Set db = CurrentDb()
Dim X As String
Set MailList = db.OpenRecordset(“email”) ‘ this is a list of emails
intI = 1
With MailList
Do Until .EOF
X = MailList.Fields(“email”).value
Call subHandleSendingEmail(“display”, X, “”, “”, “Heres your email dude!”, MyBodyText, “”) ‘ If you dont want to see the result of the email change display to send
MyTrackingTable.AddNew
MyTrackingTable(“emailaddress”) = X
MyTrackingTable(“emailsubject”) = “current Flyer”
MyTrackingTable(“DateSent”) = Now()
MyTrackingTable.Update
.MoveNext
intI = intI + 1
Loop
End With
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
x=””
End Sub
Part 2- the html body text file- edit for your links
‘———————————————————————————————————-
‘create a texfile and call it “C:\body\bodyhtml.txt” or wherever, also this the string for BodyFile$ = “C:\body\bodyhtml.txt” following this section
‘in this example there are 2 images being added to the body, you can just put one or 10 its upto you
My company Sales
We are Please to Announce This Months Newest Flyer
Your Company, products and sets are provided to customers who demand
only the rugged and toughest tools within heavey-duty Industries.
Your Company Sales
###-###-####
Toronto,Ont
postal code
To be removed from this list please reply stating you would longer want our specials.
sorry didnt format use this link to view the body text file
http://www.text-upload.com/read.php?id=207953&c=6184027
Part 3- the sub which created the mail objects and ties it all in together.Note: This is not my code, i dont know who the auther is but i did change it abit to suit my needs. Sorry Mr Author who ever you are.
Private Sub subHandleSendingEmail(sDisplayOrSend As String, _
sTo As String, _
sCC As String, _
sBCC As String, _
sSubject As String, _
sMsgBody As String, _
sAtts As String)
‘sAtts is expected to be a list of files to attach, delimited by “|” (known as a pipe)
Const olFolderOutbox = 4
Const olFolderDrafts = 16
‘This section of code will attempt to get an instance of the Outlook object using late binding.
‘If Outlook is closed the code should open Outlook.
‘If Outlook is not installed or the install is corrupted, this section of code should detect that.
On Error Resume Next
Dim oOutlookApp As Object
Set oOutlookApp = GetObject(, “Outlook.Application”)
If Err.Number 0 Then
Err.Clear
Set oOutlookApp = CreateObject(“Outlook.Application”)
If Err.Number 0 Then
MsgBox “Error: ” & Err.Number & vbCrLf & vbCrLf & _
Err.description & vbCrLf & vbCrLf & _
“Apparently you do not have Outlook installed or configured properly.”
Err.Clear
Set oOutlookApp = Nothing
Exit Sub
End If
End If
Dim oSession As Object, oMsg As Object, oAttach As Object
Dim i As Integer, sEntryID As String, sStoreID As String
On Error Resume Next
Set oSession = CreateObject(“Redemption.RDOSession”)
If Err.Number 0 Then
MsgBox “Please contact your database administrator and give him the following message:” & vbCrLf & vbCrLf & _
“There was a problem creating the RDOSession. Outlook Redemption Objects must not be installed.”
Err.Clear
Set oSession = Nothing
Set oOutlookApp = Nothing
Exit Sub
End If
oSession.Logon
Set oMsg = oSession.GetDefaultFolder(olFolderDrafts).items.Add
sStoreID = oSession.GetDefaultFolder(olFolderDrafts).StoreID
sEntryID = oMsg.EntryID
‘Multiple email addresses can be passed into the email address fields
‘by passing them into this function, separated by a semicolon
‘If you want to validate the email addresses to make sure they actually have an
‘@ symbol in them and have a domain name that’s formatted correctly, you’ll
‘need to do it before passing it into this function or do it below.
oMsg.To = sTo
oMsg.cc = sCC
oMsg.BCC = sBCC
oMsg.Subject = sSubject
‘This code will put the attachment filenames listed in sAtts into an array
‘and then attach each file as an attachment and embed the jpegs into the body.
If sAtts “1” Then ‘ used to be a zero
i = 0
If InStr(sAtts, “|”) = 0 Then sAtts = sAtts & “|” & ” ”
‘Remove any doubled up delimiters
sAtts = Replace(sAtts, “||”, “|”)
Dim aryAtt() As String
aryAtt = Split(sAtts, “|”)
Do Until i = (UBound(aryAtt) + 1)
‘Check to see if the filename is blank before attaching it
If Trim(aryAtt(i)) “” Then
‘Check to see if the file actually exists before attaching it
If Dir(aryAtt(i)) “” Then
Set oAttach = oMsg.attachments.Add(aryAtt(i))
‘If the attachment is a .jpg assume that we want to embed it in the email
If Right(aryAtt(i), 4) = “.jpg” Then
oAttach.Fields(“MimeTag”) = “image/jpeg”
oAttach.Fields(&H3712001E) = “picture” & CStr(i)
‘I’m assuming we want the pictures below the optional text that’s passed into this function
sMsgBody = sMsgBody & “”
End If
End If
End If
i = i + 1
Loop
Else
MsgBox “quitting , no file selected!”
Exit Sub
End If
oMsg.HTMLBody = sMsgBody
oMsg.Save
sEntryID = oMsg.EntryID
If LCase(sDisplayOrSend) = “send” Then
oMsg.Send
End If
oSession.Logoff
Set oAttach = Nothing
Set oMsg = Nothing
Set oSession = Nothing
If LCase(sDisplayOrSend) = “display” Then
Set oMsg = oOutlookApp.GetNamespace(“MAPI”).GetItemFromID(sEntryID, sStoreID)
Err.Clear
On Error Resume Next
oMsg.Display
If Err.Number 0 Then
MsgBox “There was a problem displaying the new email because there is a dialog box ” & _
“open in Outlook. Please go to Outlook to resolve this problem, ” & _
“then look for the new email in your Drafts folder.”
Err.Clear
End If
Set oMsg = Nothing
End If
Set oOutlookApp = Nothing
End Sub
This section below is actually the most important part what were trying to achieve.
Set oAttach = oMsg.attachments.Add(aryAtt(i))
‘If the attachment is a .jpg assume that we want to embed it in the email
If Right(aryAtt(i), 4) = “.jpg” Then
oAttach.Fields(“MimeTag”) = “image/jpeg”
oAttach.Fields(&H3712001E) = “picture” & CStr(i)
‘I’m assuming we want the pictures below the optional text that’s passed into this function
sMsgBody = sMsgBody & “”
sorry again there is html formatting in the code here is the link for all parts:http://www.text-upload.com/read.php?id=207973&c=2503758
Maybe you should down this link and view it in your IDE editor. I use Codelobster for my editor.
I have success fully sent over 1000 newsletters with this code and hope you learn that even though microsoft constantly challenges us with more and more security blocks we can eventually overcome them with everyones ingenuity and desire to take on a challenge.
I am using a query to show quotes that expired du to exceeding the original quote valid for 30 days parameter.
I am using the emaildatabase object to sendthe query results to an email address.
the email address is a field in the query result.All apears to work if i enter a an email address into the TO field.
When i try to use the email adress value from the query the email does not go with an unknown message recipient error message.
What should the syntax be when using the emaildatabase object in access 2010 to send email to the email address in the query?
=[queryname].[emailaddress] ?????
Thanks
I’m not sure what you mean. to send an email the standard way would be like-
Set db = CurrentDb()
Dim X As String
Set MailList = db.OpenRecordset(“emailâ€) ‘ list of emails
intI = 1
With MailList
Do Until .EOF
X = MailList.Fields(“emailâ€).value
send emails x()
a query cant send objects if thats what your asking.
you need to populate the list of emails then send them.
also if they are stored as hyper links you need to strip them. maybe you can elaborate a little more.
Is there an effcient way to use this code to send approx. 8,000 emails at once? I was thinking of trying to have Access create an email list in outlook, but I believe that would involve adding every recipiant to the address book, something I don’t want to do. Thanks!
I’m also having trouble with the code to track the email in a separate table. It appears that the variable is being dropped before it can be written. It has no problem with the “DateSent” since it equals now(). But using MyMail.To cause an error that says the item has been changed or deleted.
8000 at once? This won’t do it that well.
Do you know of a way to provide a “text entry field” in Access so the user can enter custom message bodies that allow formatting text in bold, italic, and Word tables?
Seems like VBA Access only allows plain text unless I use the .HTMLbody property in which case, I’m back to hardcoding the message body.
IF NOT THEN…
Is it possible for the user to save an Outlook email in Outlook’s Draft folder and then have Access call that email by Subject Line, and cycle through the “Send To” from Access list?
IF NOT THEN…
I guess I’m looking at querying a list in Access and manually copying it over to a little propriety bulk email program.
Thanks!
Many thanks for this – just what I needed for trying to move the small charity for which I work (as a volunteer) into the electronic age!
Jeff, I want to automatically schedule my ms access records to be sent to another user through ms outlook. kindly help me Jeff..
There’s so many different things at play here, I couldn’t possibly begin to list them all. It’s beyond the scope of the article. (Sorry!)
But look at autoexec macros and the task scheduler for some places to start.
Thanks for all this, Jeff. And thanks for all the great ideas everyone.
i have created the outlook mail as a draft, but i want the information from the database to be presented in the table that will be on the outlook mail. any solution?
this is my code for the BODY…..
how can i put this into a table….
OutlookMail.Body = “Flight Date:” & strFltDate.PadRight(20) & “Flight number:” & strFltNum & Environment.NewLine & _
“DEP:”.PadRight(5) & strDep.PadRight(15) & “ARR:”.PadRight(5) & strArr & Environment.NewLine & _
“REG:”.PadRight(5) & REG.PadRight(15) & “AC:”.PadRight(5) & AC & Environment.NewLine & _
“STD:”.PadRight(5) & STD.PadRight(15) & “STA:”.PadRight(5) & STA & Environment.NewLine & _
“ETD:”.PadRight(5) & ETD.PadRight(15) & “ETA:”.PadRight(5) & ETA & Environment.NewLine & _
“ATD:”.PadRight(5) & ATD.PadRight(15) & “ATA:”.PadRight(5) & ATA & Environment.NewLine & _
“Status:”.PadRight(5) & Status.PadRight(15) & Environment.NewLine & Environment.NewLine & “AFFECTED DEPARTURE PAX” & Environment.NewLine & _
“DEP_YC:”.PadRight(5) & depyc.PadRight(15) & “DEP_JC:”.PadRight(5) & depjc & “DEP_Inbound:”.PadRight(5) & depinbound.PadRight(15) & “DEP_Outbound:”.PadRight(5) & depoutbound & Environment.NewLine & “AFFECTED ARRIVAL PAX” & Environment.NewLine & _
“ARR_YC:”.PadRight(5) & arryc.PadRight(15) & “ARR_JC:”.PadRight(5) & arrjc & “ARR_Inbound:”.PadRight(5) & arrinbound.PadRight(15) & “ARR_Outbound:”.PadRight(5) & arroutbound & Environment.NewLine & Environment.NewLine & _
“Comment:”.PadRight(9) & Comment.Text & Environment.NewLine
Wow! Thank you so much for this wonderful tutorial! Do you happen to know if it is possible to add Outlook voting buttons to the email?
Hello Jeff
Many thanks for this post.
I tried the code you posted on May 13, 2007
I can only display the email, but I can’t send it.
Please help.
Hello Jeff
Its working , the problem was that outlook must be running.
Many thanks
Hello and thank you for this very useful information!! I am a new programmer, and I have made a query in Access 2007 that contains email addresses, and I want to send all of these email addresses to a BCC in a new Outlook 2010 email. I have followed all of the instructions, but I am getting a run-time error ’13’ Type Mismatch, and the debugger is highlighting “Set MyMail = MyOutlook.CreateItem(olMailItem)”.
I know that when I went to check off the “Outlook x.0 Object Model” I could not find it, but instead found “Microsoft Outlook 14.0 Object Library” and checked that instead. Could this be causing the problem, and is there something else I should be doing for Outlook 2010?
Also, it would be even better if I could simply populate the BCC: field in an Outlook email with all of the email addresses in the list and I can write my own email, but my attempts to simplify the code are not working either due to my limited understanding of coding.
Thank you to anyone who may reply, I greatly appreciate any suggestions!
Current code:
Public Function SendEMail()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Set fso = New FileSystemObject
Subjectline$ = InputBox$(“Please enter the subject line for this mailing.”, _
“We Need A Subject Line!”)
If Subjectline$ = “” Then
MsgBox “No subject line, no message.” & vbNewLine & vbNewLine & _
“Quitting…”, vbCritical, “E-Mail Merger”
Exit Function
End If
BodyFile$ = InputBox$(“Please enter the filename of the body of the message.”, _
“We Need A Body!”)
If BodyFile$ = “” Then
MsgBox “No body, no message.” & vbNewLine & vbNewLine & _
“Quitting…”, vbCritical, “I Aint Got No-Body!”
Exit Function
End If
If fso.FileExists(BodyFile$) = False Then
MsgBox “The body file isnt where you say it is. ” & vbNewLine & vbNewLine & _
“Quitting…”, vbCritical, “I Aint Got No-Body!”
Exit Function
End If
Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
MyBodyText = MyBody.ReadAll
MyBody.Close
Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
Set MailList = db.OpenRecordset(“MyEmailAddressesTEST”)
Do Until MailList.EOF
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.To = MailList(“EMail”)
MyMail.Subject = Subjectline$
MyMail.Body = MyBodyText
MyMail.Display
MailList.MoveNext
Loop
End Function
Comment out the line that says
MyMail.Display
and uncomment
MyMail.Send
I do not know about programatically adding voting buttons. People actually use them? 🙂
guys any solution to ym problem i posted earlier
I’m using your code to send an email every time a record is added to my database. I wanted to have all form fields be contained in the body of the email as well. I have it almost completely done however I cannot list the subform fields. Essentially the main form is the client information and the subform contains the requested products and their respective quantities. I’m trying to figure out a loop for this but it’s causing me nothing but headaches…
If you can provide any help, it would be much appreciated.
My code is as follows, I have it attached to an add button on my form.
Private Sub Add_Click()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim rs As DAO.Recordset
Dim Q As String
Dim Product As String
Set fso = New FileSystemObject
Subjectline$ = “SUPPLY ORDER – ” & [Client ID].Column(1)
MyBodyText = “A new supply request has been entered into the database for fulfillment by ” & [Order Requestor].Column(1) & “!” & vbNewLine & vbNewLine & “Please click on the Incomplete Supply Orders button in the database to fulfill.”
MyBodyText = MyBodyText & vbNewLine & vbNewLine & “Order Information:”
MyBodyText = MyBodyText & vbNewLine & vbNewLine & [Client ID].Column(1)
MyBodyText = MyBodyText & vbNewLine & [Address Line 1]
MyBodyText = MyBodyText & vbNewLine & [Address Line 3]
MyBodyText = MyBodyText & vbNewLine & vbNewLine & “Notes: ” & [Order notes]
MyBodyText = MyBodyText & vbNewLine & vbNewLine & “Order Specifics:”
Set db = CurrentDb()
Set rs = db.OpenRecordset(“SELECT * FROM SupplyRequests1”)
With rs
If Not .EOF And .BOF Then
.MoveFirst
Do Until .EOF
Q = .Fields(Quantity)
Product = .Fields(Product_ID)
MyNewBodyText = Q & Product
.MoveNext
Loop
End If
End With
MyBodyText = MyBodyText & vbNewLine & vbNewLine & “Thank you!”
Set rs = Nothing
Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
Set MailList = db.OpenRecordset(“MyEmailAddresses”)
Set MyMail = MyOutlook.CreateItem(olMailItem)
Do Until MailList.EOF
MyMail.Recipients.Add MailList(“email”)
MailList.MoveNext
Loop
MyMail.Subject = Subjectline$
MyMail.Body = MyNewBodyText
MyMail.Send
Set MyMail = Nothing
Set MyOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
On Error GoTo Err_Add_Click
DoCmd.GoToRecord , , acNewRec
Exit_Add_Click:
Exit Sub
Err_Add_Click:
MsgBox Err.Description
Resume Exit_Add_Click
End Sub
Ok, so I didn’t really get a good answer and was going to give it a try again. I have a form that has an employee and all information. On the form I also have the different categories they can fit in. Some employees fit into multiple categories so I used a Yes/No box to select which categories they fit in. I had to add a column for each of the 14 possible categories they can fit into. I do not want to add space to my database that I don’t need so I don’t really want to have 14 queries. I’d like to have a list box that I can make a selection of multiple categories and pull up all employees that are under this category. Can anyone help me?
Hello,
Thank you for all this wonderful help, here. My question is how to attach different Word or Excel files based on a query field value (Employee, Supervisor, Director, Aide)?
So far, I have one attachment (Employee.docx). But, how does one write:
If a query field value contains ‘Employee’, attach ‘Employee.doc’,
If a query field value contains ‘Supervisor’, attach ‘Supervisor.doc’
etc?
Thank you!
Set db = CurrentDb()
Set MailList = db.OpenRecordset(“queryA”)
With MailList
If .EOF And .BOF Then
MsgBox ” No emails will be sent ”
Else
Do Until MailList.EOF
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.To = MailList(“fE-mail”)
MyMail.Subject = “Subject Line”
MyMail.Body = “Hi ” & MailList(“fFirstName”) & “,” & vbNewLine & vbNewLine & “Text Body”
MyMail.Attachments.Add “C:\Documents\Employee.docx”, olByValue, 1, “My Displayname”
MyMail.Display
MailList.MoveNext
Loop
End If
End With
Simple.
The attachment is just a string, so you can do whatever string manipulations you want.
So, instead of:
MyMail.Attachments.Add “C:\Documents\Employee.docxâ€, olByValue, 1, “My Displaynameâ€
Use
MyMail.Attachments.Add “C:\Documents\” & MyMail(“QueryField”) & “.docx”, olByValue, 1, “My Displaynameâ€
Brilliant! I just had to make a little adjustment to:
MyMail.Attachments.Add “C:\Documents\” & MailList(“QueryField”) & “.docx”, olByValue, 1, “My Displayname”
Thank you, Jeff!
This is great! I’ve gotten it working up until the part where I need to change the code to read the tokens. I get a Compile error: variable not defined. Where in the sample code does this piece go?
‘ This line will copy the “master” template into
‘ a variable we can mess around with
MyNewBodyText = MyBodyText
‘ Now we can replace tokens to our heart’s content
‘ without worrying about corrupting the “master” template
MyNewBodyText = Replace(MyNewBodyText, “[[FirstName]]”, MailList(“FirstName”))
MyNewBodyText = Replace(MyNewBodyText, “[[NumberOfUnits]]”, MailList(“NumberofUnits”))
Could you possibly post the code with the tokenizing built in?
Thanks a million.
I placed it here, and I get that compile error.
‘This gives it a subject
MyMail.Subject = Subjectline$
‘ Now we can replace tokens to our heart’s content
‘ without worrying about corrupting the “master” template
MynewBodyText = MyBodyText
MynewBodyText = Replace(MynewBodyText, “[[Display Name]]”, MailList(“DisplayName”))
MynewBodyText = Replace(MynewBodyText, “[[ArrivalDate]]”, MailList(“ArrivalDate”))
MynewBodyText = Replace(MynewBodyText, “[[DepartureDate]]”, MailList(“DepartureDate”))
‘This gives it the body
MyMail.Body = MyBodyText
Never mind, I got it. I forgot to add “Dim MynewBodyText As String” at the top.
Is there a way to flag the email as high importance?
You just need to use the importance property on the mail item.
So put this near the MyMail section:
MyMail.importance = olImportanceHigh
‘can be olImportanceNormal, olImportanceHigh or olImportanceLow
Hi Jeff, I am using this code to great effect (but without an email address so that the user can select one when the outlook email opens with attachment), however, when the outlook window opens, if the user then decides they dont want to email the report, and clicks ‘x’, the rest of the macro stops, how can i get the macro to keep running if they decide not to send the email?
Thanks!
Ah, forget that sorry, I added in an error handler! Doh!
Hi Jeff Knapp
I was looking for an answer as to how to find an existing email programme should it be other than Outlook when I stumbled across the above –
I am compelled to compliment you on the extraordinary dedication you have shown in answering all the questions posed.
Few on the web go to so much trouble – well done.
I have developed a routine for sending emails (had I come across this article I may well have adapted it for my use.)
For what its worth I have an email form called from various reports sending the reports (pdf format) as attachements – the report may go to just one or as many recipents as desired.
Its very useful to have textboxes for the message, olTO, olCC and OLBCC visible as one as adds to the message, and builds up the recipients from combo boxes
I gives one full control when Adding to/deleteing single or all – etc- etc – before sending – I am a bit like the individual above whose typos are somewhat numerous
regards and compliments again
db (ps – I am not looking for you to answer my problem :))
Hi – I am new to VB programming and am trying to learn about using the Module part of Access. I am getting the message: Can not find the function.
I have checked spelling etc and the module appears in my DB. Any ideas PLEASE
Where are you getting this error?
I have set up a macro with the Action: RUNCODE and put the argument as the module name eg =SendEmail()
The message Access immediately gives is:”The expression you entered has a function name that Microsoft Access can’t find.” I have a non-empty module saved as this name.
Jeff – thanks for your code. One question – how can I put the recipient string in the BCC: field instead of the To: field?
Lynn,
I am having the same problem. I have tried renaming it several different ways, but am having no luck.
Sorry for the delay in approving/replying, but just use .BCC instead of .TO
The function needs to be marked as PUBLIC in a code module to be called from a macro. Its not the best practice to name the module the same as a routine contained inside it.
Morning Jeff, and this is an awesome write up. Thanks for all your work.
I’ve created a popup with text fields for all the email data (TO, BCC etc)and pull those values into your code block in order to generate the email. No problem. I cannot, however, figure out how to attach a PDF file created in the same access DB without writing it to the harddrive (i.e. the way access does it if I generate an email straight from a macro). Can you tell me how to append the result of a report from my DB to the email generated using your code?
My best regards
Hi,
I would like to know if the code that you have provided can set the email account to use. We have several email accounts in outlook and I want to define one specific email account for sending the email. There is nothing like myMail.from command as far as I checked.
Thanks,
You have to set the email account via the Profiles applet. So, whatever the default email Outlook runs under is the one it will send from.
I’d lay odds that Access writes it out to a temp file first.
Hi Jeff, your article is awesome! I have few questions.In my Navigation form there is a button. Onclick of that button opens up a report. This report has email address of different people against the job codes, job nature etc. I want to write a VBA code where if I click run, it will trigger emails to these individuals along with their job codes, job nature, current job etc. Could you please help me. I am not a developer. Please help.
Hi Jeff,
I am very interested in using this method to send an email from access, with a slight difference.
I have created the query you mentioned but would like to be able to do the following.
Use the query to generate the Subject text and Body text using fields from my database (don’t require additional input as I have a subject field which includes text and data from the dataset together).
I don’t require group email as the macro will be run by pushing a button on the subject form, to advise the client that their request has been actioned (single email), and provide the relevant data and references from the form.
Unfortunately, I am not quite sure how to modify your code to reference the three fields from my query:
Email
Subject
BodyText
Could you advise (I imagine the code would be simpler than that used previously).
Hoping you can help, and thanks either way. If nothing else you show that it can be done.
Great Code. I implemented in my transferspreadsheet function to resolve issue with SendObject limitation when dealing with spreadsheets (limits to 255 characters even with memo field). Thanks!
Jeff,
First, thanks for the help. First time I tried any VBA coding and I got my emails working!
But they look drab. I don’t like Plain Text formatting. I simply want to use a Word document that allows for bold, underline, highlight, etc… (no images).
I read all 221 posts and I realize you really like TXT files and not Word or HTML. But you are soooo good at what you do (yes-that’s flattery), can you please let me know what I need to change in your code to use a .doc template?
I have a database of audit observations. When the due date (a column in the database) is approaching, I would like the database to send an email to the person responsible for the observation. Maybe set up a macro to send an email to all persons where due date is less than 30 days? Is this possible?
Any assistance would be appreciated.
Thanks,
Lena
I’m new at this and Access and all this been helping me train myself… Thanks
Hi Jeff
I need to automate an email to be sent to the student from an examination board using tool such as Microsoft Access or Excel. The email will vary as how well or how bad the students did in the exam, pass or fail. How do I go about creating an architecture for that?
I am creating a access database that I will be using for a user to submit request. I have created my forms and am having issues linking a email to the button. I want my form to be sent to myself, and a email to send to the user to acknowledge I have revieced the request. In the form I am having the user type in there email, and would like for it to be pulled from the form.
For simple tasks, a little VBA and the use of the DoCmd.SendObject command should do the trick. Simply take the values from the form and email it.
Take a look at our paper:
DoCmd.SendObject Command in Microsoft Access: Features and Limitations for Sending Emails
http://www.fmsinc.com/MicrosoftAccess/Email/SendObject.html
Hope this helps.
Has anyone answered Deirdre’s post (#191)?
Okay, I’ve been fiddling with this for a bit and I’ve got everything working but I can’t figure out two things:
1. How can I link the data to a form I made?
2. How can I make the mailing list only choose from the record I currently have open on the form?
I’d like to be able to send a fairly standardized email to people within the database but I need to change a few things within the email such as a follow up date that I would contact them again. As far as I can tell, my main problem is that I can’t sort the data out by using the currentrecord tool because it’s pulling from the beginning of the database and then it loops till the end instead of starting from the current record and ending after a single run. Does anyone have ideas how this could be accomplished?
Sorry about this. I should have sent more information to help you guys out. This is what I have so far. The form I’m trying to pull from is named Info and I’m trying to just get the current record. For some reason it’s always saying that I’m improperly using the “me!” command. If anyone can help that would be greatly appreciated.
Private Sub cmdemailinfo_Click()
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Set appOutLook = CreateObject(“Outlook.Application”)
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
Set objOutlookRecip = .Recipients.Add(Me.info![email])
.BodyFormat = olFormatRichText
.Subject = “subject”
.HTMLBody = “text here”
‘.Attachments.Add (“C:\Users\Craig\Desktop\info.pdf”)
”.DeleteAfterSubmit = True ‘This would let Outlook send the note without storing it in your sent bin
MailOutLook.Display
End With
End Sub
A quick couple of notes, I’m running windows XP, Office 2010, I’m completely updated with Microsoft Updates, and I believe I have all the correct references in VB because the email will open if I turn the line with the address into a comment and run the script without it.
Got it figured out by actually finding a database sample that was very useful. It was a lot more involved than I realized. Here’s the link if anyone is interested in taking a look at it.
http://msdn.microsoft.com/en-us/library/office/ee208547%28v=office.12%29.aspx
Hi,
i am trying to send attachment by email through access 2007. It saves the attachment documents on a folder. But it is not sending by email. But it gives error message like this: “error 2147024894 – can not find this file. verify the path and file name is correct”
I think it is not get the path name. How i can solve this issue?
The code is as below:
Private Sub Command22_Click()
Dim dbs As Database
Dim rst As Recordset, rsattach As Recordset
Dim Attachment_filename As String, savepath As String
Dim strSQL As String
Dim strMsg As String
Dim strTo As String
Dim strBcc As String
Dim strcc As String
Dim strSubj As String
Dim strBody As String
Dim strReport As String
Dim strAddresses As String
strSubj = “Invitation”
strBody = “Dear sir/madam,” & “” & “” & _
“Greetings,” & “” & “” & _
“Regards,”
strReport = “APPLICANTREPORT”
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“SELECT * FROM EVALDETAILQ WHERE (EVALDETAILQ.EVALID)= ” & Me.EVALID)
Set rsattach = rst.Fields(“CVDETAIL”).Value
Do While Not rsattach.EOF
rsattach.Fields(“FileData”).SaveToFile “C:\Users\admin\Desktop\test\”
Attachment_filename = rsattach.Fields(“Filename”)
savepath = “C:\Users\admin\Desktop\test” & rsattach.Fields(“Filename”)
rsattach.MoveNext
Loop
DoCmd.OpenReport strReport, acViewPreview, , “EVALID=” & Me.EVALID, acHidden
DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, “C:\Users\admin\Desktop\test\APPLICANTREPORT.Pdf”
Debug.Print savepath
strTo = Me.SCREENERID.Column(3)
Do Until rst.EOF
”With rs
” Do While Not .EOF
” StrBcc = !Email & “;” & StrBcc
strBcc = strBcc & rst(“EMAIL”) & “;”
rst.MoveNext
Loop
If Len(strBcc) > 0 Then
strBcc = left$(strBcc, Len(strBcc) – 1)
End If
EMailAsPDF strReport, savepath, strTo, strBcc, strSubj, strBody
DoCmd.CLOSE acReport, strReport
rst.CLOSE
dbs.CLOSE
Set rsattach = Nothing
Set rst = Nothing
Set dbs = Nothing
End Sub
I believe you’re missing a backslash when you’re making the path which is why it can’t find the file, because, as the error says, it can’t find the file. 🙂
savepath = “C:\Users\admin\Desktop\test†& rsattach.Fields(“Filenameâ€)
You need a trailing \ after test” and before & rsattach…
Hi,
Thanks for thr reply. Then how i can get the path? Can you modify the code as per requirement and help me to sort out the issue?
Regards
Wasim
Hi,
I tried as per your suggestion. But message is same. It could not find the path. Can check the deatil and again? I miss something?
waseem
Don’t see anything else. What does your “debug.print savepath” line reveal? A valid path? If you copy the result and paste it into a RUN box, does a document open up?
hi,
I tried in run command below code:
“C:\Users\admin\Desktop\test\” & “\Attachment_filename”
It is not showing any. If i put only “C:\Users\admin\Desktop\test\” then it shows all the files in that folder. If i use this in access, it says this is folder and we can’t attach the folder.
can you suggest me how i can get the files in my email?
waseem
I’m looking for a way to restart feature. for example, I have 50 states and I want to start at 26 and go to the end. all 50 states are set up in a table.
Thanks,
Awesome codes! Has someone figured out formatting in the BodyText yet? What if instead of reading a .txt file, can someone help me with the code to insert an Autotext already in my Outlook.