Automated Email / Mail Merge

[kazaccess]

I'm not really finding any helpful solutions through my own search process, and I was wondering if you guys would be able to help me.



I have a query containing information about students that has the following columns: [NAME], [CLASS], [GROUP], [EMAIL ADDRESS]. I also have a word document containing a standardized message that is to be emailed to students who exist in the query.

What would be the easiest way to automatically prepare and/or send these emails to those students so that the message body reads:

Dear [NAME],

You have been enrolled in [CLASS] and placed in the [GROUP] group.

[ItsMe]

Do you nead the message in the body of the email or can it be in an attachment like a PDF?

[kazaccess]

Either way would be acceptable. If attaching a PDF would be easier, then that could be done.

[ItsMe]

You can create a report and then attach the report to an email like this.

     DoCmd.OpenReport "rptName", acViewPreview, , strCriteria, acHidden
        DoCmd.SendObject acReport, "rptName", acFormatPDF, "ToEmailAddress", "CcEmailAddress", "BccEmailAddress", "Email Address Subject Line", "Text to Include within Email Body", True
        DoCmd.Close acReport, "rptName"

Alternatively, you can send an email without an attachment. If you choose to do this using Outlook you will need to construct some objects and manipulate Outlook. Then you can concantinate fields within a string that is the body of the email message.

SOmething like this

Dim appOutLook As Object
Dim MailOutLook As Object
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(0)

With MailOutLook
    .BodyFormat = 2
    .To = "sample@domain.com"
    ''.cc = ""
    ''.bcc = ""
    .Subject = "Subject Line"
    .HTMLBody = "THis is the body of the Email" & Me.ControlName.value
    .DeleteAfterSubmit = False 'This would let Outlook send the note without storing it in your sent bin
    .ReadReceiptRequested = True
    .send
    
End With

I suggest you practice sending a single email and then take the next step. Sending a single email is good but when you want ot send multiple emails that are unique to rows in a recordset, you will need to iterate a recordset, includeing similar code to send an email for each iteration.

What I am saying is choose a method, one with an attachment or one without. Try and get a single email sent with some sort of static data/message and then report back for answers to questions and or further instruction.

[kazaccess]

Thank you for the help. I am able to send a single email and am trying to figure out how to do this for each record in the query (loop through each record). Is there a simple snippet of code that I can use and then insert the code I have that sends a single email?

[ItsMe]

The simplest would probably be to use a form's recordset. Let's say you are looking at a form and there are 12 records in the form's recordset. 2 of the 12 are not supposed to receive an email. You can filter those two out using the intrinsic tools offered by the form. Now that the form is displaying the correct (10) records you can fire some code to create a recordset clone. You can iterate (loop) through the array and send a single email for each of the ten records.

'Here is an example of looping through al the records in a form's recordset
Dim rs as DAO.Recordset
Set rs = Me.RecordsetClone
If rs.EOF = False Then
rs.MoveFirst    'We are at the first record so do not move next yet
    Do Until rs.EOF

    Debug.print rs![NameOfField]     'Here you will want to include the name of your field
    ' You would insert your email code here
    'It can get a little tricky because you will need to 
    'get the values from your various fields and either use them to 
   'filter a report or concatenate the fields in a string for the body of 
    'the email
    rs.MoveNext
    Loop
    
Else
MsgBox "No records Found!"
Set rs = Nothing
End If
Set rs = Nothing

After the code runs you can look at the field value in the immediate window. Debug . print will display in the immediate window. You can view the immediate window by pressing Ctrl+G on your keyboard.

[kazaccess]

I'm getting errors saying that there is an invalid reference to the Me.RecordsetClone property. Below is the code I am using to send 1 email. Perhaps my problem lies there?
Dim olApp As Object
Dim objMail As Object

On Error Resume Next 'Keep going if there is an error


Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open


If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
End If

'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)

With objMail

'Set body format to HTML
.BodyFormat = olFormatHTML
.To = [Reports]![MY REPORT]![EMAIL FIELD]
.Subject = "Subject"
.HTMLBody = "Text"
.Save

End With

[ItsMe]

Go ahead and paste the code you are using. I do not see any DAO code in your post. Please use the advanced options when posting your code and enclose the code in the special "code tags". Click "Go Advanced", and then use the hashtag to place the special code tags in your post. Now paste your code inside the tags.

.


.

[kazaccess]

Dim olApp As Object
Dim objMail As Object

On Error Resume Next 'Keep going if there is an error


Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open


If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
End If

'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)

With objMail

'Set body format to HTML
.BodyFormat = olFormatHTML
.To = [Reports]![MY REPORT]![EMAIL FIELD]
.Subject = "Subject"
.HTMLBody = "Text"
.Save

End With

[ItsMe]

Well, that can not be all of the code. All you did was post the same code. Thanks for using the brackets though.

I tested the code and got the following to work.

Dim olApp As Object
Dim objMail As Object
'On Error Resume Next 'Keep going if there is an error

'Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open

'If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
'End If
'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
'.BodyFormat = olFormatHTML
.bodyformat = 2
'.To = [Reports]![MY REPORT]![EMAIL FIELD]
.To = "Address@Domain.com"
.Subject = "Subject"
.HTMLBody = "Text"
.Save
.send
End With

You can not include the name of an object as your To address. In other words, [Reports]![MY REPORT]![EMAIL FIELD], will not work. If you are trying to debug with a line like On Error Resume Next, you will not know where the problem is.

[kazaccess]

Sorry, I didn't specify on my last post. I am able to get that part working fine. I am just not sure how to implement code that will do that for every record in the query/report.

[ItsMe]

Did you try something and it failed? Your posts do not compile in a logical manner. If you tried some code and it failed, post THAT code. If you are stuck and don't know what to do next, explain what you tried. Simply stating, "Me.Recordsetclone" did not work will not suffice. My response to that will be something like, "It works for me".

[kazaccess]

Sorry, I'm kind of all over the place with this one. Taking the code you gave me using the DAO, I have inserted my email code that sends a single email. My problem is that I do not know how to properly insert my code so that it will iterate through every record, given the code you provided.

YOUR CODE


Dim rs as DAO.Recordset
Set rs = Me.RecordsetClone
If rs.EOF = False Then


rs.MoveFirst    'We are at the first record so do not move next yet
Do Until rs.EOF


Debug.print rs![EMAIL FIELD]     'Here you will want to include the name of your field


MY CODE


Dim olApp As Object
Dim objMail As Object


On Error Resume Next 'Keep going if there is an error


Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open


If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
End If


'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)


With objMail
'Set body format to HTML
.BodyFormat = olFormatHTML
.To = [Reports]![MY REPORT]![EMAIL FIELD]
.Subject = "Subject"
.HTMLBody = "My email body."
.Save
.Send


End With
  

 YOUR CODE


rs.MoveNext
    Loop
    
Else
MsgBox "No records Found!"
Set rs = Nothing
End If
Set rs = Nothing

[ItsMe]

I took the code from post # 6 and combined it with code from post # 10. I tested it in a new blank DB. It worked fine. I suggest you try doing the same. Take this code and place it in a new form (bind the form to a table or query add a couple of controls like a button for a click event and a textbox with the PK) and test it. Read the comments in the code. Try and understand what is going on. You will need to adjust a couple of things in order for it to work, the name of the field and the email To address.

Dim olApp As Object
Dim objMail As Object   ' I moved the declarations outside of the loop
'Here is an example of looping through al the records in a form's recordset
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
If rs.EOF = False Then
rs.MoveFirst    'We are at the first record so do not move next yet
    Do Until rs.EOF
    Debug.Print rs![NameOfField]     'Here you will want to include the name of your field
    'You can use the immediate window to view the results of looping through the recordset
    'Ctrl+G will display the imeddiate window
    
    ' You would insert your email code here
    'It can get a little tricky because you will need to
    'get the values from your various fields and either use them to
   'filter a report or concatenate the fields in a string for the body of
    'the email
'************************************************************************************************
'Insert some email code here
'
'Dim olApp As Object
'Dim objMail As Object   ' I moved the declarations outside of the loop

'On Error Resume Next 'Keep going if there is an error
'Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open
'If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
'End If
'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
'.BodyFormat = olFormatHTML
.bodyformat = 2
'.To = [Reports]![MY REPORT]![EMAIL FIELD]
.To = "Address@Domain.com"  'Keep this static for now while we test
.Subject = "Subject"
.HTMLBody = "Text " & rs![NameOfField]  'Let's include some dynamic data in the body
.Save
.send
End With
'************************************************************************************************
    rs.MoveNext
    Loop
    
Else
MsgBox "No records Found!"
Set rs = Nothing
End If
Set rs = Nothing

[kazaccess]

This helped a lot. Thank you so much! I am able to create the correct number of emails with the dynamic data. There is one final thing I cannot figure out. How can I make the Email recipient object dynamic? I have been adjusting the Field Name in the .To part of the code in order to do this, but so far all of the emails are being addressed to the first person in the recordset.