Follow Up Email

**drunkenneo** · 07-25-2013, 10:53 PM

I am having a table from where email is send to customers, and update the table as response send as true if customer replies back,

I need to set up a followup email when ever customer didn't replied in 5 days, on replying upon previous email, could this is possible in VBA?

**June7** · 07-26-2013, 12:14 AM

Probably. Open a recordset in VBA of records that don't have response. Loop through the recordset, send email.

**drunkenneo** · 07-26-2013, 12:38 AM

yes, i want to have a followup email setup for it as a trail mail along with the previously send email.

**June7** · 07-26-2013, 01:32 AM

So what do you need to know? Do you already have code for sending emails?

**drunkenneo** · 07-28-2013, 10:00 PM

yes, i do have code for it, but my main challenge is a trail mail, where i want to add followup content on previously sent email, as not with the new one again.

**June7** · 07-28-2013, 10:58 PM

What method are you using to send email? Show code.

**drunkenneo** · 07-28-2013, 11:15 PM

This is the code

Public Sub WordXPort()

'CREATING PDF FILES FOR ATTACH AND MAIL DRAFT-START

Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim fld3 As Field
Dim strMessage1 As String
Dim strMessage As String
Dim str1 As String
Dim ctr1 As Integer
Dim str3 As String
Dim table As Object
Dim oLook As Object
Dim oMail As Object
Dim i As Integer
Dim counter As Integer
Dim Count As Long
Dim Progress_Amount As Integer
Dim strTableBeg As String
Dim strTableEnd As String
Dim strFntNormal As String
Dim strFntHeader As String
Dim strFntEnd As String
Dim a As Hyperlink

'intialsing counters
mlcount = 0
Count = DCount("order_number", "Redemption_Report")

'Storing column values to field variables
Set rs = CurrentDb.OpenRecordset("select * from Redemption_Report")
Do Until rs.EOF
Set fld3 = rs.Fields("order_number")
SysCmd acSysCmdInitMeter, "Files getting generated ...", Count
For Progress_Amount = 1 To Count
SysCmd acSysCmdUpdateMeter, Progress_Amount
'Opening Word Application to add contents
Set rs1 = CurrentDb.OpenRecordset("select DISTINCT Tracking_No from Master_Table where order_number= " & fld3 & " And emailsent_Date is Null")

Set rs2 = CurrentDb.OpenRecordset("select * from Email_Status where order_number= " & fld3 & " And email_sent = False")

'Define format for output
strTableBeg = "<HTML><table style='border:1px solid black;border-collapse:collapse'>"
strTableEnd = "</table></HTML>"
strFntHeader = "" & _
"<tr>" & _
"<td style='border:1px solid black;text-align:center;'>AWB</td>" & _
"</tr>"
strFntNormal = ""
strFntEnd = ""
strMessage = strTableBeg & strFntNormal & strFntHeader
Do Until rs1.EOF
strMessage = strMessage & _
"<tr>" & _
"<td style='border:1px solid black;text-align:center;'>" & rs1![Tracking_No] & "</td>" & _
"</tr>"
rs1.MoveNext
Loop

strMessage = strMessage & strFntEnd & strTableEnd
str1 = "Dear " & rs![contact_first_name] & " " & rs![contact_last_name] & "," & " " & _
"Thank you for placing your order " & rs![order_number] & " with I ovider." & " " & _
"Please note that we have dispatched " & rs![quantity] & "Gift Card(s) to the below mentioned address via <a href=""http://www.aramex.com/"">Aramex</a> with the given AWB number"
str3 = "Address: " & rs![bizname] & " " & " " & rs![delivery_address_1] & " " & " " & rs![delivery_address_2] & " " & " " & rs![delivery_address_3] & " " & " " & rs![delivery_town] & ", " & rs![delivery_postcode] & " " & " " & rs![delivery_county] & " " & _
"Kindly confirm via return email once you are in receipt of these cards in order for us to activate the card(s) with the ordered amount" & " " & _
"If you have any other questions about any aspect of your order or your account, please quote your order number and business ID and send an email to asdad@i.com." & " " & _
"Alternatively, you may complete the online " & Chr(34) & "Contact Us" & Chr(34) & " form from your account pages." & " " & _
"Kind Regards" & " " & "logy Provider Points Team" & " " & _
"<IMG alt='' hspace=0 src='cid:MyPic.jpg' align=baseline border=0>"

'OPENING OUTLOOK AND GENERATING MAIL CONTENT WITH ATTACHMENT-START
Set oLook = CreateObject("Outlook.Application")
myRecipient = rs![email_address]
If IsNull(myRecipient) = False Then
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
With oMail
.To = myRecipient
.Subject = "Your IProvider Order " & rs![order_number] & ""
.BodyFormat = olFormatHTML
.HTMLBody = strFntNormal & str1 & " " & strMessage & " " & str3
.Save
End With
End If
Set oLook = Nothing
Set oLook = Nothing
'OPENING OUTLOOK AND GENERATING MAIL CONTENT WITH ATTACHMENT-END
mlcount = mlcount + 1
rs1.Close

Do Until rs2.EOF
rs2.Edit
rs2![Email_Sent] = True
rs2![EmailSent_Date] = Now()
rs2.Update
rs2.MoveNext
Loop

rs.MoveNext
Next Progress_Amount
SysCmd acSysCmdRemoveMeter
Loop
rs.Close
'CREATING PDF FILES FOR ATTACH AND MAIL DRAFT-END
End Sub

only for writing email, its not for auto populate when customer replies back.

**June7** · 07-29-2013, 01:27 AM

If you want to add 'followup content' why not just have an If Then statement after the message text you already have to concatenate more text? Or have the conditional construct a completely different text. The only real trick is passing a criteria to the procedure to use in the conditional code.

**drunkenneo** · 07-29-2013, 02:20 AM

Hmm...lost ..
could you please explain more clearly

**June7** · 07-29-2013, 03:05 AM

Maybe have a checkbox on form or some other criteria (maybe calculated from a date).

str4 = " followup content text here"

Then:

.HTMLBody = strFntNormal & str1 & " " & strMessage & " " & str3 & IIf(Me.followupcheckbox, str4, "")

There's a variety of ways to deal with this. Another version:

If Me.followupcheckbox Then
.HTMLBody = str4
Else
.HTMLBody = strFntNormal & str1 & " " & strMessage & " " & str3
End If

**vincent-leeway** · 07-29-2013, 06:55 AM

Is trigger not allowed here?

**June7** · 07-29-2013, 04:11 PM

What do you mean by 'trigger'?

Follow Up Email

Thread Tools

Follow Up Email

Similar Threads

Cursor to follow SetFocus

Follow-up Reminder

Follow Up

Follow up calls, put in automatic date

FOLLOW UP DATABSE

Posting Permissions