Email multiple contacts and attach a report specific to each contact

[bre1603]

I have a continuous form in Access 2007 that lists contacts for different agencies. Each record has a checkbox that I can use to generate a mass email to all selected contacts – it’s generic with no subject, no attachments and no message, for everyday use. However, the database owners would also like the ability to email a report to each contact (asking them to verify and update their information) using the checkboxes on this form.

Ideally, they would select the contacts they wanted to email, click the “Email Info Update Form” button and have Outlook send out an email to each of the contacts with their Contact Info Report attached. (I can address whether the emails go automatically or get reviewed later – I’m really just interested in getting the report attached to each email.)

Here's what I have-

This code (it's rough) opens multiple emails, although there seems to be a limit to the quantity. But I can' get the report to attach to more than one record-the one that has the focus at the time the button is clicked (that's why it's commented out):

Private Sub Command18_Click()
 
Dim ol As Object
Dim msg As Object
Dim rec As DAO.Recordset
Dim intCount As Integer
Dim intLoop As Integer
 
Set ol = CreateObject("Outlook.Application")
 
Set rec = CurrentDb.OpenRecordset("qryLeadershipEmailListALL SelectQuery")
If Not (rec.BOF And rec.EOF) Then
rec.MoveLast
rec.MoveFirst
intCount = rec.RecordCount
For intLoop = 1 To intCount
Set msg = ol.CreateItem(olMailItem)
msg.Subject = "ENTER YOUR SUBJECT HERE"
msg.To = rec("Leadership Email")
msg.Body = "Message goes here."
msg.Body = msg.Body & Chr(13) & Chr(13) & "It's a really long message."
 
'DoCmd.OpenReport "AgencyUpdateForm", acViewPreview, , "[Agency Number]= forms![LeadershipEmailListALL].[Agency Number]"
'DoCmd.SendObject acSendReport, "AgencyUpdateForm", acFormatPDF, msg.To, , , msg.Subject, msg.Body
 
'msg.Attachments.Add "AgencyUpdateForm"
', acViewPreview, , "[Agency Number]= forms![LeadershipEmailListALL].[Agency Number]"
msg.Display
 
Set msg = Nothing
rec.MoveNext
Next intLoop
End If
 
Set rec = Nothing
Set ol = Nothing
 
End Sub

This code attaches the report but only opens an email for the first contact:

Private Sub Command43_Click()
On Error GoTo Err_Command43_Click
 
Me.Refresh
 
Dim strEMail As String
Dim strAddr As String
Dim dbs As DAO.Database
Dim rstEMail As DAO.Recordset
Dim stTo As String
Dim stCC As String
Dim stBcc As String
Dim stMessageSummary As String
Dim stSubjectSummary As String
 
'Retrieve all E-Mail Addressess
Set dbs = CurrentDb
Set rstEMail = dbs.OpenRecordset("qryLeadershipEmailListALL SelectQuery")
 
With rstEMail
  Do While Not .EOF
    'Build the Recipients String
    strEMail = strEMail & ![Leadership Email] & ";"
      .MoveNext
  Loop
End With
 
 
stSubjectSummary = "Agency Contact Update Request for " & Me.Agency_Name
 
    stMessageSummary = "Message goes here." & ""
    stMessageSummary = stMessageSummary & Chr(13) & Chr(13) & "It's a really long message"
 
'Determine the stTo line
 
    stTo = [Leadership Email]
    stCC = ""
    stBcc = ""
 
rstEMail.Close
Set rstEMail = Nothing
 
DoCmd.OpenReport "AgencyUpdateForm", acViewPreview, , "[Agency Number]= forms![LeadershipEmailListALL].[Agency Number]"
DoCmd.SendObject acSendReport, "AgencyUpdateForm", acFormatPDF, stTo, stCC, stBcc, stSubjectSummary, stMessageSummary
 
 
Exit_Command43_Click:
    Exit Sub
 
Err_Command43_Click:
    MsgBox "No contacts selected. Please try again."
    Resume Exit_Command43_Click
End Sub
 

Between the two, I feel like I'm close. But I've been working at this too long, and I'm beginning to think I'm missing something obvious.

Any input would be much appreciated. Thanks!

[Delerna]

Don't know the cause of your issues and I couldn't problem solve the posted code, it's too complicated a problem to debug code in a forum post. That's why access has a debugging tool, so you can see what the code is doing as it is being executed and you can tell why it's not doing what you thought it would do when you wrote it.

Not my strong point either but I dabble with this sort of stuff every now and then.
I don't know if what I do is optimal, best practise or not. I do try to do things optimally but my underlying philosophy is if it works and it's performance is acceptable then that will do, move on to the next project.

Anyway here is something I have done in the past that maybe you can find usefull here ??

Maybe if you output the report as a file and then attach the file to the email

    DoCmd.OutputTo acOutputReport, "rptCustomersClearanceLetter", acFormatSNP, "BOL\CCL.SNP"
    DoCmd.OutputTo acOutputReport, "rptCustomersClearanceLetter", acFormatRTF, "BOL\CCL.rtf"
 

I generally get best results with the snp file

[bre1603]

@Delerna
I'm trying to get around saving each report, as there are around 400 contacts in the database.

Plus I don't think my users are going to want to go find 40 reports and attach them to the 40 emails they want to send out monthly (they want to send the 400 emails in waves of 40).

I'm fairly certian of the user's reluctance to this, because I already built in a way to send an email to one contact at a time with the report attached automatically (using the SendObject method), and that's not "easy enough" for them.

Hence my quest to do it by bulk. Ah, I love users...

[Delerna]

I was actually suggesting that you programmatically attach the files to the emails similar to what you are trying to do with the report or perhaps via CDO. I was not suggesting for your users to go find them and attach them. If I was a user I would baulk at that too!

However if you say it won't work then it wont work...... Anyone else?

[bre1603]

It sort of works... but only in combination with the SendObject method. And it attaches the same page of the report to every email. I want it to attach different pages - each agency should get it's own contact info attached.

There also seems to be some limit to the number of emails I can generate at once.

I'll keep trying.

[Delerna]

Just been reading through the code you say you can't get the report to attach to more than one record.

Your code
1) creates a recordset from qryLeadershipEmailListALL

I presume that that query is selecting all the email addresses that the user has selected on the form

2) for each record retrieved
it runs the report based on the value in forms![LeadershipEmailListALL].[Agency Number]
and emails it



It seems to me that
forms![LeadershipEmailListALL].[Agency Number] is not the right place to get that value from because it will allways retrieve the same value. The value in the record that happens to have focus when the email is sent. This is exactly what you say is happening....I think.


Can you not retrieve the value you need in the qryLeadershipEmailListALL along with the email address. Then you can do this

DoCmd.OpenReport "AgencyUpdateForm", acViewPreview, , "[Agency Number]=" & rec("Agency Number")
 

Also you may need to close that report after it has been sent

I hope I am understanding your post correctly

[Delerna]

Also, Why do you say there seems to be a limit to the quantity of emails.

I think you might mean because it didn't retrieve that last contact selected.

If that is the case then are you aware that changes to a record on an access form are not actually updated to the table until after you move away from that record.

This means that if a user ticks 1 contact and then immediately clicks the email button, then that tick will not have been updated to the table yet and no-one would get emailed.

If you ticked 2 contacts and then clicked the email button, only 1 email would occur...the first one ticked.

If 3 were ticked then only the first 2 would get emailed.

I don't know how you have designed it so I may be way off here but it may be an answer

[bre1603]

I tried retreiving the value for [Agency Number] from the qryLeadershipEmailListALL, but I received the error**:
"Property not found. Runtime error 3270" when I reference rec("Agency Number"). My code looks exactly as yours above does, but I also tried it with brackets around [Agency Number], to no avail.

**Error is actually 3464, Data type mismatch in criteria expression.


Also, I think you're right about the problem I'm having getting emails to open for all the selected records. It's also because I wasn't accounting for null values in the email address field.

[Delerna]

you don't want to put the square braces in
rec("Agency Number") it is correct as it is.

The error suggests to me that the field [Agency Number] either does not exist in the query or it is spelled differently.

Check the query design and ensure that it is being selected in the query "qryLeadershipEmailListALL SelectQuery"

[bre1603]

I checked the query and the spelling, everything seems to match up. I'll include my current code - maybe you'll see something I can't.

Dim ol As Object
Dim msg As Object
Dim rec As DAO.Recordset
Dim intCount As Integer
Dim intLoop As Integer
 
 
Set ol = CreateObject("Outlook.Application")
 
Set rec = CurrentDb.OpenRecordset("qryLeadershipEmailListALL SelectQuery")
If Not (rec.BOF And rec.EOF) Then
rec.MoveLast
rec.MoveFirst
intCount = rec.RecordCount
For intLoop = 1 To intCount
Set msg = ol.CreateItem(olMailItem)
msg.Subject = "Contact Information for: " & rec("Agency Name")
msg.To = rec("Leadership Email")
msg.CC = ""
msg.Body = "This is a periodic request for your up-to-date information with which to contact your agency."
msg.Body = msg.Body & Chr(13) & Chr(13) & "If you have any questions, please reply or call me at 555-555-5555."
 
DoCmd.OpenReport "AgencyUpdateForm", acViewPreview, , "[Agency Number]=" & rec("Agency Number")
DoCmd.OutputTo acOutputReport, "AgencyUpdateForm", acFormatPDF, "F:\" & [Agency Name] & ".pdf"
DoCmd.SendObject acSendReport, "AgencyUpdateForm", acFormatPDF, msg.To, , , msg.Subject, msg.Body
 
msg.Display
 
Set msg = Nothing
rec.MoveNext
Next intLoop
End If
 
Set rec = Nothing
Set ol = Nothing
 
End Sub

The code errors at line 23.

Please tell me it's just a misplaced comma or something...

[Delerna]

looking at that now I think I see the problem.
I copied your code for rec("Leadership Email") and changed the field name. I didn't notice that this is not right.

it should be
rec.fields("Leadership Email")
and
rec.fields("Agency Number")

I am puzzled now as to why your original
rec("Leadership Email")
did not generate the same error

[bre1603]

I tried adding .fields to both lines of code, but it had no effect. I still get that same error, "Type mismatch."

It occurred to me though...does it matter that [Agency Number] is a text field? The "numbers" range from "011" to "310 A01" to "956" - could this be the causing the error? the field is a text field throughout (in table, query, form, and report), but perhaps in this code I'm not accounting for that? Just a thought...

[bre1603]

I've got the code successfully opening an email window for the selected contacts. And it's attaching what I told it to (when I alter/take out the rec.Fields() part...) But it's not limiting the report it's outputting - so the entire report gets attached to each email.

With the rec.fields() included, I get the Data Type Mismatch error. Without it, I get the whole report, since that's where I'm trying to apply a filter on the report as it's opened. I'm going to attach my updated code since I was tweaking it today. I really don’t know where to go from here.

Dim ol As Object
Dim msg As Object
Dim rec As DAO.Recordset
Dim intCount As Integer
Dim intLoop As Integer
Dim dbs As DAO.Database
Dim strAgNo As String
 
Set ol = CreateObject("Outlook.Application")
Set dbs = CurrentDb
Set rec = dbs.OpenRecordset("qryLeadershipEmailListALL SelectQuery")
 
If Not (rec.BOF And rec.EOF) Then
rec.MoveLast
rec.MoveFirst
intCount = rec.RecordCount
For intLoop = 1 To intCount
Set msg = ol.CreateItem(olMailItem)
msg.Subject = "Contact Information for: " & rec("Agency Name")
msg.To = rec.Fields("Leadership Email")
msg.CC = ""
msg.Body = "This is a periodic request for your up-to-date information with which to contact your agency."
msg.Body = msg.Body & Chr(13) & Chr(13) & "If you have any questions, please reply or call me at 555-555-5555."
strAgNo = rec.Fields("Agency Number")
 
DoCmd.OpenReport "AgencyUpdateForm", acViewPreview, "[Agency Number] =" & rec.Fields("Agency Number")
DoCmd.OutputTo acOutputReport, , acFormatPDF, "F:\" & [Agency Name] & ".pdf"
'DoCmd.SendObject acSendReport, "AgencyUpdateForm", acFormatPDF, msg.To, , , msg.Subject, msg.Body
DoCmd.Close acReport, "AgencyUpdateForm", acSaveNo
 
msg.Attachments.Add "C:\Documents and Settings\bleb107\Desktop\Initial Email Msg.doc"
msg.Attachments.Add "F:\" & [Agency Name] & ".pdf"
msg.Display
 
Set msg = Nothing
rec.MoveNext
Next intLoop
End If
 
Set rec = Nothing
Set ol = Nothing

Thanks for your help.

[Delerna]

I not sure at this stage how to assist you. As I said previously it's difficult to problem solve code on a forum post. I cannot see the database in it's entirety, or set breakpoints so as to monitor what is happening as it runs. The database probably contains emails that you wouldn't and shouldn't want to make available to anyone by attaching the database here.



The problem is here

 "[Agency Number] =" & rec.Fields("Agency Number") 
 

When you originally had it as

 "[Agency Number] = forms![LeadershipEmailListALL].[Agency Number]") 
 

Did the report obtain the correct data for that agency number?
If it did then you should be able to get this working!





The error is saying that the data type for this [Agency Number]
does not match the data type for this rec.Fields("Agency Number")
ie .. [Agency Number] is expecting an int
and rec.Fields("Agency Number") is being interpreted as string .. for example.

What is the data type of [Agency Number]?

Some things to try if [Agency Number] is string

 "[Agency Number] ='" & rec.Fields("Agency Number") & "'"
 
or
 
 "[Agency Number] ='" & rec.Fields("Agency Number").value & "'"
 

Some things to try if [Agency Number] is int

 "[Agency Number] =" & cint(rec.Fields("Agency Number")) 
 
or
 
 "[Agency Number] =" & cint(rec.Fields("Agency Number").value) 
 

[bre1603]

So I was exploring my thoughts on the problem stemming from the fact that [Agency Number] is a text field... And I came up with what you did -

"[Agency Number] ='" & rec.Fields("Agency Number") & "'" 
 

And that worked - I got three emails to open with the correct attachement for each! However, all three attachments had the same name (which should be the Agency Name, therefore different for each attachment).

Is there a way to solve this do you think? At worst, I'll just make the attachment name something generic, but I'd like it to be the Agency Name if possible. Here's the code where I name it:

DoCmd.OutputTo acOutputReport, , acFormatPDF, "F:\" & [Agency Name] & ".pdf" 
 

Thanks for all your help!

[Delerna]

Is agency name in the query?

DoCmd.OutputTo acOutputReport, , acFormatPDF, "F:\" & rec.Fields("Agency Name") & ".pdf" 
 

[bre1603]

That seemed obvious - reference the rec.fields("Agency Name")... But it still snuck past me. Thanks!

I've got the whole thing working like a dream now. Thank you so much for all your help. You were a life-saver!