Email from Access to Outlook, user response? (1 Viewer)

[rustyuser]

I have the following code that gets data from a query then will send an email to roughly 150 people. I want them to reply and verify the information that I have in the database.

What I would like is for them to respond easily. I was wondering if it was possible to put 2 buttons in the body of the email? one button that replies to the message with text that says "all information is complete and valid", and a second button that would essentially just click reply - then they could just respond whatever way they wanted. I'm thinking most will hit the "All is OK" button, so I would like that as automated as possible so I'm not waiting forever for people to respond.

Or is there some easier way that I'm missing. And/or is it even possible to put that kind of button in an outlook email?

here's the code I have:


Private Sub cmdSendEmail_Click()

Dim objOutlook As Object
Dim objMailItem As Object

Dim strSubject As String
Dim strFrom As String
Dim strBody As String
Dim intJV As Integer
Dim rstEmail As DAO.Recordset

'LOOP THROUGH A RECORDSET TO SEND AN EMAIL TO EACH RECORD
Set rstEmail = CurrentDb.OpenRecordset("qryEmailRegistration")
rstEmail.MoveLast
rstEmail.MoveFirst

intJV = 0
For intJV = 0 To rstEmail.RecordCount - 1

'EMAIL FROM HERE

strFrom = """School Records ""<recordsupdate@school.com>"

'EMAIL SUBJECT HERE

strSubject = "School Information Verification"

'EMAIL BODY HERE
strBody = "To The " & rstEmail!FamilyLastName & " Family:" & vbCrLf & vbCrLf & "As you may know, the school has implemented a new school database." _
& " This database will aid in automating the school directory, school mailings, the One Call Now™ system, and various other reports and office records containing the student and family information." _
& vbCrLf & vbCrLf & "It is important that the information be accurate, so we ask that you please verify the details below that are currently in the database. Please reply to this email with any changes/updates." _
& vbCrLf & vbCrLf & "Thank you." & vbCrLf & vbCrLf & "Please respond to this email ASAP. " _
& vbNewLine & "* * * * * * * * * * * * * * * * * * * *" _
& vbNewLine & "Information on file for the " & rstEmail!FamilyLastName & " Family..." & vbCrLf & vbCrLf & vbTab & "Primary Address: " & rstEmail!FamilyStreetAddress & " " & rstEmail!FamilyCity & ", " & rstEmail!FamilyZipcode & vbCrLf & vbCrLf & vbTab & "Primary Phone Number: " _
& vbTab & rstEmail!PrimaryTelephone & vbCrLf & vbCrLf & vbTab & "Mother's Name: " & rstEmail!MotherFirstName & vbCrLf & vbCrLf & vbTab & "Mother's Work Phone: " & rstEmail!MotherWorkNo & vbCrLf & vbCrLf & vbTab & "Mother's Cell: " _
& rstEmail!MotherCellNo & vbCrLf & vbCrLf & vbTab & "Mother Additional eMail: " & rstEmail!MotherEMail2 & vbCrLf & vbCrLf & vbTab & "Father's Name: " & rstEmail!FatherFirstName & vbCrLf & vbCrLf & vbTab & "Father's Work: " _
& rstEmail!FatherWorkNo & vbCrLf & vbCrLf & vbTab & "Father's Cell: " & rstEmail!FatherCellNo & vbCrLf & vbCrLf & vbTab & "Father Additional eMail: " & rstEmail!FatherEMail2 _
& vbNewLine & vbNewLine & "* * * * * * * * * * * * * * * * * * * *" _
& vbNewLine & "Your Primary Address and Primary Telephone Number ONLY will be published in the directory. Please let me know if you do not want your information listed." & vbCrLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf & Chr(13) & Chr(10)

Set objOutlook = CreateObject("Outlook.application")
Set objMailItem = objOutlook.CreateItem(0)

With objMailItem
.To = rstEmail!MotherEMail
.SentOnBehalfOfName = strFrom
.SUBJECT = strSubject
.Importance = 2
.Body = strBody

.Display
SendKeys "%{s}", True
End With

Set objMailItem = Nothing
Set objOutlook = Nothing

rstEmail.MoveNext
Next intJV

End Sub

Thank you!!

 

[HiTechCoach]

TIP: It helps to use the code forum tags with your VBA code.

Example:

' your code here
   If 2 > 1 then 
       ' do something
   End If

 

[NigelShaw]

Hi

I've never tried and I don't intend to however, have you tried adding a button on an email in outlook and assess any code available there? You might be able to copy it over or do something.

Another alternative might be to add a HTML email and add a submit type button, set the button to email back to you through HTML and then copy the HTML into the body of your vba code.

Like I said, I've never tried it but a couple of options nonetheless



Nidge

 

[Kryst51]

Just an idea, not sure it will work, but if you make and e-mail template with the buttons on it. And code the buttons, (Not sure how)

Then have your access code send that template, merging your info and e-mail to your 150 people using that template with the buttons already coded.

I know that doesn't help with the code, but could help with problem solving some.

 

[rustyuser]

so after some Outlook research, there are voting options you can send with an email. {when you are sending an email, click on "Options", then there is a Voting and Tracking Options section.} That seemed the easiest way to handle it.

Anyway, I just included in my code:

.VotingOptions = "Info Correct; See Changes"

in the With objMailItem bit. Worked great! Just wanted to let you know.

Thanks all.