Automated Email from List (1 Viewer)

BennyLinton

Registered User.
Local time
Today, 03:00
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
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:00
Joined
Aug 30, 2003
Messages
36,118
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.
 

BennyLinton

Registered User.
Local time
Today, 03:00
Joined
Feb 21, 2014
Messages
263
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


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.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:00
Joined
Aug 30, 2003
Messages
36,118
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.
 

BennyLinton

Registered User.
Local time
Today, 03:00
Joined
Feb 21, 2014
Messages
263
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
 

Anakardian

Registered User.
Local time
Today, 11:00
Joined
Mar 14, 2010
Messages
173
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:00
Joined
Sep 21, 2011
Messages
14,038
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 ?

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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:00
Joined
Sep 21, 2011
Messages
14,038
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
 

BennyLinton

Registered User.
Local time
Today, 03:00
Joined
Feb 21, 2014
Messages
263
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
 

BennyLinton

Registered User.
Local time
Today, 03:00
Joined
Feb 21, 2014
Messages
263
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"
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:00
Joined
Aug 30, 2003
Messages
36,118
Glad you got it working. Sorry for deserting you, I goofed off and played golf yesterday. :D
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:00
Joined
Aug 30, 2003
Messages
36,118
Presuming it's Null:

rs!firstName & " " & (rs!middleName + " ") & rs!lastName & " " & vbNewLine & _
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:00
Joined
Aug 30, 2003
Messages
36,118
No problem. That takes advantage of the fact that + propagates Nulls, & does not.
 

BennyLinton

Registered User.
Local time
Today, 03:00
Joined
Feb 21, 2014
Messages
263
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"
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:00
Joined
Aug 30, 2003
Messages
36,118
You can embed HTML tags in the text, and use

.HTMLBody

instead of

.Body
 

Users who are viewing this thread

Top Bottom