Email Each Page of Report to Different Person

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report of leads. I want to email each lead to the appropriate
agent. Is that possible? This is how I see it working....The user clicks a
button on a form. It brings up a report for each agent then sends the email
or something like that. There will be several.
 
Hi,

You could make a macro for it and then use the sendobject.
Here you can specify which report to send e.g. in HTML to whom.

The only problem you will have is that you will have to run it lead by lead
to create an email per email.

the 2nd problem you will have his how to specify the lead for the report now
this is easily to be solved

What you could do is have a temp table which you use to select the records
to be used for the report e.g. SELECT Lead_Name, Lead_Detail FROM Leads INNER
JOIN T_Leads on Leads.Lead_Name=T_Leads.Lead_Name

Now to solve problem 1 you could have the following in vba

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM T_Leads"
DoCmd.RunSQL "INSERT INTO T_Leads (Lead_Name) VALUES ('Lead1')"
DoCmd.SendObject ObjectType:=acSendReport, ObjectName:="LeadReport",
Outputformat:="HTML", To:="(e-mail address removed)", Subject:="Your leads for today"
DoCmd.SetWarnings True

I hope this give you some ideas.
 
I found a way to send email in a VBA book (Access 2003 VBA). When I ran it,
the only thing that came through on the email was "Business Name". I think
this can work if I can figure out how to get multiple leads in the body. Am
I on the right track or just crazy? Here is the code:

Option Compare Database

Sub LeadEmail()
Dim objOutlook As New Outlook.Application
Dim objEmail As Outlook.MailItem

Dim strLtrContent As String
Dim rsContacts As New ADODB.Recordset

rsContacts.ActiveConnection = CurrentProject.Connection
rsContacts.Open "qry_A65Leads"

'for each record in the qry_A65Leads query, send an email

Do While Not rsContacts.EOF

strLtrContent = "Dear " & rsContacts("AGTNM") & " / " & ("AGTNO") & ":"
& vbCrLf & vbCrLf
strLtrContent = strLtrContent & "The Marketing Center recently called
your clients turning age 64½ to see if they were interested in meeting with
you to discuss Medicare Supplement Insurance. These calls were made FREE of
charge as part of the Age 65 Cross Sell program. As a result of these
calls, the following clients are interested in meeting with you. They are
expecting a follow-up call from you so it is important that you call them
within 24 hours to schedule an appointment."

strLtrContent = strLtrContent & vbCrLf & vbCrLf

strLtrContent = "Client Name" & vbTab & "Client Address" & vbTab &
"Phone Number" & vbTab & "Birth Date" & vbTab & "Comments" & vbCrLf
strLtrContent = strLtrContent & ("PHC_NM") & vbTab & ("ADDR") & vbCrLf &
("CityStateZip") & vbTab & ("HM_PHONE") & vbTab & ("DOB") & vbTab &
("COMMENTS") & vbCrLf

strLtrContent = "Click here to learn more about services provided
through the Age 65 program (you must have a Passport session open for this
link to work)."

strLtrContent = strLtrContent & vbCrLf & vbCrLf & "Thank you," & vbCrLf
& vbCrLf

strLtrContent = "Joe Smith" & vbCrLf
strLtrContent = "Supervisor, Marketing Center" & vbCrLf
strLtrContent = "555-555-5555(P) / 555-555-5555(F)" & vbCrLf
strLtrContent = "Business Name" & vbCrLf

'create an email regarding any Age 65 lead

Set objEmail = objOutlook.CreateItem(olMailItem)
objEmail.Recipients.Add rsContacts("Email")
objEmail.Subject = "Age 65 Leads"
objEmail.Body = strLtrContent

'send the message
objEmail.Send

'move to the next contacts record
rsContacts.MoveNext

Loop

End Sub
 
Back
Top