Sending Multiple Emails Using MSAccess and MSOutlook

[RobertIngles]

Hi all;
I am fairly proficient with Access however I am totally inept at VBA. I have been asked to help take a load off of our IT appointment bookers. I would like to send automated emails to users reminding them of their upcoming laptop upgrade appointments. There could be anywhere from 10-100 on site upgrades done in a day and I would like to be able to send all users with an appointment a reminder a day or two before our techs arrive to ensure they have their laptop available to be worked on at the location indicated in their user file. I would like to send the reminders out via a single command button. I know how to set up the query to select the users and their email addresses to be used however need to understand the coding/process for Access to send out all of the reminders.
Thanks in advance for your help!!

 

[MaxieMax]

if you create a module using the below script you could then put an action behind the button you want to use to loop throughyour query result and for each row send out an email to each user by calling the function.

script below -

Public Function EMAIL(StrEmailRecipient As String, StrEmailRecipientCC As String, StrEmailTitle As String, StrEmailMsg As String)
Dim objOutlook As Outlook.Application
Dim objNameSpace As NameSpace
Dim objFolder As MAPIFolder
Dim objEmail As Outlook.MailItem
Dim StrEmailAddress(1) As String
Dim StrBodyText As String
Dim StrTitle As String
Dim StrEMail As String


On Error GoTo DoNotSend

StrEmailAddress(0) = StrEmailRecipient
StrEmailAddress(1) = StrEmailRecipientCC

StrTitle = StrEmailTitle
StrBodyText = StrEmailMsg

' creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNameSpace.GetDefaultFolder(olFolderInbox)
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
.To = StrEmailAddress(0)
.CC = StrEmailAddress(1)
.Subject = StrTitle
.HTMLBody = StrBodyText
.Send
End With


'Closes outlook. remove you do not want to close outlook
' objOutlook.Quit

Set objOutlook = Nothing
Set objNameSpace = Nothing
Set objFolder = Nothing
Set objEmail = Nothing

Exit Function

 

[MaxieMax]

Oh Sorry you will also need to add a reference to the microsift outlook 11.0 object Library

hope that all helps.

 

[RobertIngles]

Thanks Maxie - I am fairly good at using all of Access's built-in functions and wizards however I am just in the process of learning how to use VBA and SQL code and what the structure of the code means (on like page 8 of learn VBA in 24 hours...lol). so I have saved your script so I revisit when I have some understanding of exactly how and where to use it!
Thanks again Maxie