RE: Email Each Page of Report to Different Person

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


"JaRa" wrote:

> 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:="lead1@xxxxxxxxx", Subject:="Your leads for today"
> DoCmd.SetWarnings True
>
> I hope this give you some ideas.
>
> --
> - Raoul Jacobs
>
> The nature of developping is sharing knowledge.
>
>
> "Laura" wrote:
>
> > 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.
.

Relevant Pages

  • Re: centrally manage defragmenation
    ... This one works on XP & 2003 and generate a txt report ... 'Determine the Error code returned by Defrag for the current drive and save ... valid switches and drives)" & vbCRLF ... dim strComputer ...
    (microsoft.public.windows.server.general)
  • Re: centrally manage defragmenation
    ... This one works on XP & 2003 and generate a txt report ... valid switches and drives)" & vbCRLF ... administrator, there is a problem loading the resource DLL, or a defrag engine could not be accessed. ... dim strComputer ...
    (microsoft.public.windows.server.general)
  • Re: centrally manage defragmenation
    ... This one works on XP & 2003 and generate a txt report ... 'Determine the Error code returned by Defrag for the current drive ... command line for valid switches and drives)" & vbCRLF ... dim strComputer ...
    (microsoft.public.windows.server.general)
  • Re: Dim oRpt As New CrystalReport1() failing
    ... I do not work with strongly typed reports - I create report files ... Dim paramFields As New ParameterFields ... > Dim paramFields As New ParameterFields ... > reference to "Dim oRpt as New CrystalReport(), ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Dim oRpt As New CrystalReport1() failing
    ... I do not work with strongly typed reports - I create report files ... Dim paramFields As New ParameterFields ... > Dim paramFields As New ParameterFields ... > reference to "Dim oRpt as New CrystalReport(), ...
    (microsoft.public.vb.crystal)