Automated Email from List

B

BennyLinton

Registered User.
Local time
Today, 05:37
Joined
Feb 21, 2014
Messages
263
I have a an Automated Email from List that is working great, except for two problems. It pulls from a table called tblContacts with three fields: Email (Y/N), FirstName, and fldEmailAddresses:

1: When the recipient receives the email it list all others that received the same email

2: I need the body of the email accept a variable for their FirstName after the hardcoded "Dear

Code: 
Dim rs As Recordset
Dim s As String, listOfMails As String

On Error Resume Next

    Set oApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then

         Set oApp = CreateObject("Outlook.Application")
        Started = True
    End If
    
Set oItem = oApp.CreateItem(olMailItem)

With oItem
    s = "SELECT tblcontacts.fldEmailAddress" & _
    " FROM tblcontacts " & _
    " WHERE (((tblcontacts.Email)='Y'));"
    
    Set rs = CurrentDb.OpenRecordset(s)
    listOfMails = ""
    While Not rs.EOF
        listOfMails = listOfMails & rs(0) & ";"
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing

    .To = listOfMails
    
    .Subject = "Test"
    
    .Body = "This is a system-generated e-mail, please do not reply. For support please refer to:" & vbNewLine & _
      "Dear it has come to our attention that you have" & vbNewLine & _
      "Should you have another further inquiries, please do not hesitate to contact us" & vbNewLine & _
          " " & vbNewLine & _
          "Test"

    .Send
End With

     Set oItem = Nothing
       If Started Then
           oApp.Quit
    End If

MsgBox "An Automatic Email has been sent.", vbOKOnly

End Sub
 
You want to send a separate email to each person, rather than a single email to all of them? Instead of building a string of recipients, move the emailing code inside the loop and send to that person.

For number 2, add the first name field to your recordset SQL and it will be available for use building the body.
 
Correct, a separate email for each recipient. I tried using
Code: 
Dear" & rs.firstName & "it has come to our attention
but it said member not found in the error (I did add the Firstname field to my SQL string). And moved the specifics into the loop, but it just sent to the first person in the list. Here is my revised code:

Code: 
Private Sub btnEmail_Click()

Dim rs As Recordset
Dim s As String, listOfMails As String

On Error Resume Next

    Set oApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then

         Set oApp = CreateObject("Outlook.Application")
        Started = True
    End If
    
Set oItem = oApp.CreateItem(olMailItem)

With oItem
    s = "SELECT tblcontacts.fldEmailAddress, tblcontacts.Email, tblcontacts.FirstName" & _
    " FROM tblcontacts " & _
    " WHERE (((tblcontacts.Email)='Y'));"
    
    Set rs = CurrentDb.OpenRecordset(s)
    listOfMails = ""
    
    While Not rs.EOF
        listOfMails = listOfMails & rs(0) & ";"
        
    .From = "GCDF"
    .To = listOfMails
    
    .Subject = "Test"
    
    .HTMLBody = "<font size=3 color=red font face=TimesNewRoman>This is a system-generated e-mail, please do not reply. For support please refer to:" & vbNewLine & _
                "<font size=4 color=black font face=TimesNewRoman>Dear it has come to our attention that you have" & vbNewLine & _
                "<font size=4 color=black font face=TimesNewRoman>Should you have another further inquiries, please do not hesitate to contact us" & vbNewLine & _
                 " " & vbNewLine & _
                "<font size=4 color=black font face=TimesNewRoman>GCDF"

    .Send
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
    

End With

     Set oItem = Nothing
       If Started Then
           oApp.Quit
    End If

MsgBox "An Automatic Email has been sent.", vbOKOnly

End Sub


pbaldy said:
You want to send a separate email to each person, rather than a single email to all of them? Instead of building a string of recipients, move the emailing code inside the loop and send to that person.

For number 2, add the first name field to your recordset SQL and it will be available for use building the body.
Click to expand...
 
You're still building the address string, you don't want to do that. Just use the current recordset value in the To. You also need the complete email code within the loop: create, build, send. If you're getting an error on that name, it isn't the correct field name from the table.
 
Like this?:
Code: 
.To = rs.fldEmailAddress

I simplified the code (getting rid of the html for now). I double-checked the spelling of the fields. I guess I'm not following how to enclose the create-build-send differently than below:

Code: 
Private Sub btnEmail_Click()

Dim rs As Recordset
Dim s As String, listOfMails As String

On Error Resume Next

    Set oApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then

         Set oApp = CreateObject("Outlook.Application")
        Started = True
    End If
    
Set oItem = oApp.CreateItem(olMailItem)

With oItem
    s = "SELECT tblcontacts.fldEmailAddress, tblcontacts.Email, tblcontacts.FirstName" & _
    " FROM tblcontacts " & _
    " WHERE (((tblcontacts.Email)='Y'));"
    
    Set rs = CurrentDb.OpenRecordset(s)
    listOfMails = ""
    
    While Not rs.EOF
        listOfMails = listOfMails & rs(0) & ";"
        
    .From = "GCDF"
    
    .To = rs.fldEmailAddress
    
    .Subject = "Test"
    
    .Body = "Dear" & rs.firstName & "it has come to our attention"

    .Send
        rs.MoveNext
    Wend
    
    rs.Close
    Set rs = Nothing

End With

     Set oItem = Nothing
       If Started Then
           oApp.Quit
    End If

End Sub
 
Did you consider using BCC?


you only need to create one e-mail and none of them can see the other receivers unless you put them in the normal To and CC fields.
 
That is not going to work as the O/P wants to personalise the emails with recipients first name.

Otherwise the O/P could use

Dear Client/Customer ?

Anakardian said:
Did you consider using BCC?


you only need to create one e-mail and none of them can see the other receivers unless you put them in the normal To and CC fields.
Click to expand...
 
Try something along the lines of
Code: 
Private Sub btnEmail_Click()

Dim rs As Recordset
Dim oApp as Object

On Error Resume Next

    Set oApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then

         Set oApp = CreateObject("Outlook.Application")
        Started = True
    End If
    

    s = "SELECT tblcontacts.fldEmailAddress, tblcontacts.Email, tblcontacts.FirstName" & _
    " FROM tblcontacts " & _
    " WHERE (((tblcontacts.Email)='Y'));"
    
    Set rs = CurrentDb.OpenRecordset(s)
    listOfMails = ""
    
    While Not rs.EOF
		Set oItem = oApp.CreateItem(olMailItem)
		With oItem
		.From = "GCDF"
		.To = rs.fldEmailAddress
		.Subject = "Test"
		.Body = "Dear" & rs.firstName & "it has come to our attention"
		.Send
        rs.MoveNext
    Wend
    
    rs.Close
    Set rs = Nothing

End With

     Set oItem = Nothing
       If Started Then
           oApp.Quit
    End If

End Sub

I would also add an error section.

HTH
 
Thanks all!... with everyone's help I was able to get the below working perfectly. Do note I had to use the ! mark after rs like below. Also I could use .HTMLbody to stylize this but chose to stick with .body instead simply because with so many browsers out there and so many mobile devices i wanted to keep it simple:

Code: 
.To = rs!fldEmailAddress

Final that works, just plan to add more error handling:

Code: 
Dim rs As Recordset
Dim oApp As Object

On Error Resume Next

    Set oApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then

         Set oApp = CreateObject("Outlook.Application")
        Started = True
    End If
    

    s = "SELECT tblcontacts.fldEmailAddress, tblcontacts.Email, tblcontacts.FirstName" & _
    " FROM tblcontacts " & _
    " WHERE (((tblcontacts.Email)='Y'));"
    
    Set rs = CurrentDb.OpenRecordset(s)
    listOfMails = ""
    
  If Not (rs.EOF And rs.BOF) Then

  rs.MoveFirst

     Do Until rs.EOF = True
        Set oItem = oApp.CreateItem(olMailItem)
        With oItem

        .From = "GCDF"
        
        .To = rs!fldEmailAddress
        
        .Subject = "Test"
        .Body = "Dear " & rs!firstName & ", it has come to our attention"
        .Send
        End With

        rs.MoveNext
        
      Loop

  Else
    MsgBox "There are no records in the recordset."
  End If
    
    rs.Close
    Set rs = Nothing

     Set oItem = Nothing
       If Started Then
           oApp.Quit
    End If
    
End Sub
 
One last thing, I need to have a way of displaying the middle name if there is one and a single space if there is not one in this line (whole block also pasted):

Code: 
"" & rs!firstName & "  " & rs!middleName & "  " & rs!lastName & "  " & vbNewLine & _

Code: 
  .Body = "This is a system-generated e-mail, please do not reply unless changes are needed to your Name." & vbNewLine & _
                    " " & vbNewLine & _
                    " " & vbNewLine & _
                    "Dear " & rs!firstName & "," & vbNewLine & _
                    " " & vbNewLine & _
                    " " & vbNewLine & _
                    "Please carefully review your complete name below as it will appear on your printed certificate:" & vbNewLine & _
                    " " & vbNewLine & _
                    " " & vbNewLine & _
                    "" & rs!firstName & "  " & rs!middleName & "  " & rs!lastName & "  " & vbNewLine & _
                    " " & vbNewLine & _
                    "Thank you"
 
Glad you got it working. Sorry for deserting you, I goofed off and played golf yesterday. :D
 
Presuming it's Null:

rs!firstName & " " & (rs!middleName + " ") & rs!lastName & " " & vbNewLine & _
 
No problem. That takes advantage of the fact that + propagates Nulls, & does not.
 
How can I stylize the text, e.g. making the first line red, all of the text Calibri font, and bold for one of the lines?:

Code: 
            .Body = "This is a system-generated e-mail, please do not reply unless changes are needed to your Name." & vbNewLine & _
                    " " & vbNewLine & _
                    " " & vbNewLine & _
                    "Dear " & rs!FirstName & "," & vbNewLine & _
                    " " & vbNewLine & _
                    " " & vbNewLine & _
                    "Please carefully review your complete name below as it will appear on your printed certificate:" & vbNewLine & _
                    " " & vbNewLine & _
                    " " & vbNewLine & _
                    "" & rs!FirstName & " " & (rs!MiddleName + " ") & rs!LastName & " " & vbNewLine & _
                    " " & vbNewLine & _
                    "Thank you, GCDF Staff"
 
You can embed HTML tags in the text, and use

.HTMLBody

instead of

.Body
 
You must log in or register to reply here.

Similar threads

B
Outlook - Filtered Unread, Server duplicate emails & think Unread = False not work so errors
Replies
6
Views
442
bignose2
B
T
Attache to email from attachment Field
Replies
2
Views
228
theinviter
T
oxicottin
Solved Out Of Memory error when emailing
Replies
8
Views
463
oxicottin
oxicottin
M
Multiple attachments and a report in the same email
Replies
15
Views
1,526
Marshall Brooks
M
A
Sending Email SMTP setting
Replies
7
Views
1,421
ions
I

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom