Automation of Emailing Individual Reports by Group ID

[mike_222]

Scope:

I'm working on a project where I must send roughly a thousand individual reports to a thousand different email recipients with .pdf attachment.

I've found some useful info on this site, and I know I'll need to add a table that designates the email address by SHIP_TO_CODE. Then create a form with the email subject, body, etc.. I don't neccessarily have to have the reports saved to a folder; I really just need them emailed to each account.

I was thinking I could modify the code some to accomplish my goal, but I'm not sure what to put. Maybe add a SendObject in there somewhere...?


PLEASE HELP!

CODE that I have now that saves the report to a folder. (I want to modify some to send email attachement instead)

''Module CODE:

Private Sub Form_Current()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [SHIP_TO_CODE] FROM [qryWty&PendingData];", dbOpenSnapshot)

Do While Not rst.EOF
strRptFilter = "[SHIP_TO_CODE] = " & Chr(34) & rst![SHIP_TO_CODE] & Chr(34)

DoCmd.OutputTo acOutputReport, "rptDraft", acFormatPDF, "C:\Users\mrutherford\Desktop\ASC Daily Reports" & "\" & rst![SHIP_TO_CODE] & ".pdf"

DoEvents
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub


''Open & Close Event CODE:

Private Sub Report_Close()
strRptFilter = vbNullString
End Sub

Private Sub Report_Open(Cancel As Integer)
If Len(strRptFilter) <> 0 Then
Me.Filter = strRptFilter
Me.FilterOn = True
End If
End Sub

 

[pbaldy]

If you don't need the file saved, you can replace the OutputTo line with SendObject. You can get the address from the recordset if the query can be modified.

 

[mike_222]

Thanks Paul. I'll try to tinker with it. Sorry for double post...I wasn't sure if you guys worked on weekends, and I must have this done for work by Monday. Our main data person has quit on us, so I have to step in with limited knowledge to pick up the pieces. I couldn't do it without your & others' help.

Thanks for all your input.

What about the subject & body of the email? I can make an email form linked to each account for the subject & body of the email, but how do I modify the code to incorporate the form?

Thanks,
-Mike

 

[pbaldy]

Depends on your needs. If it's fixed, you can just include in the code something like "Attached is your statement" or whatever. If you want to specify it you can get it from the form: Me.TextboxName

By the way, I'm not sure the current event of the form is appropriate, unless you've arranged it so it can't fire more than once.

 

[mike_222]

Yes..The subject & message body will be fixed for every report. I'm just not sure how to write the code to make it do that. I'm currently looking for examples online.

 

[pbaldy]

Just put the text in quotes within the SendObject arguments.

 

[mike_222]

Right...I found the sytax, but it won't let me post it on here because of some stipulation with this site. Says, "To be able to post email addresses, your post count must be 10 or greater..."

 

[mike_222]

expression .SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)

 

[mike_222]

My question is do I put the email field name in the "To" section?

 

[pbaldy]

Yes; if you've got it in the recordset:

Rst!FieldName

 

[mike_222]

This is what I have, but it's not working:

Private Sub Form_Current()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [SHIP_TO_CODE] FROM [qryWty&PendingData];", dbOpenSnapshot)

Do While Not rst.EOF
strRptFilter = "[SHIP_TO_CODE] = " & Chr(34) & rst![SHIP_TO_CODE] & Chr(34)

DoCmd.SendObject acOutputReport, "rptDraft", acFormatPDF, rst!Email Addresses, , , "TEST Subject", "Test Message", , False

rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub

 

[pbaldy]

You haven't included the email field in the recordset SQL. If there's a space in the field name, it has to be bracketed.

 

[mike_222]

Ok So I renamed the field in the table and removed the space now, and I had already tied the table to the qryWty&PendingData, so it is in the record set, but still not working..

 

[mike_222]

You're right, it is saying "item not found" for the field, but i linked the email table to the query the report it based on, but it is still not tied to the record set. How to I tie it to the record set?

 

[pbaldy]

It is not automatically in the recordset. You have to add it:

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [SHIP_TO_CODE], EmailAddress FROM [qryWty&PendingData];", dbOpenSnapshot)

 

[mike_222]

Ahhh I see. Ok i added it and it worked, well it recognized the field. But when I executed it, the emails pop up on my monitor for me to send manually. Each one popped up and i had to press "send". How can i automatically send them all?

 

[pbaldy]

The EditMessage argument should control that. Is it set correctly? If so, is Outlook or other email client open?

 

[mike_222]

Yes, you're right...again..But now I'm getting an outlook warning saying, "A program is trying to send an email message on your behalf...." uuuggghhhh....

 

[pr2-eugin]

Yes, you're right...again..But now I'm getting an outlook warning saying, "A program is trying to send an email message on your behalf...." uuuggghhhh....

That is the Default Security feature of MS Outlook, I think introduced in 2007 and above.. There are couple of ways to turn off this feature, some require Third party software and some going into Administrative mode to Run Outlook, and under Options you can disable the feature..

 

[mike_222]

Yeah...downloaded some 3rd party software called ClickYes, and it seems to have solved my problem. Thanks for all of your help!

-Mike