Macro- to send an email to specific people in a list 2

[Delboy14]

Hi,
I am trying to create a macro, which will send a personailsed email to a list of people whose details are held in a table. (ie Tableeople Fields: name,address, email...).

Is there a way to use a macro send the email to each person in the list, and use their personal details for each letter.

The letter will be in form of a report.

I understand this is a long question any help would be great especially on the email part. Thanks in advance

 

[grnzbra]

It's been a while since I worked with macros, but I do something like what you want using VBA. I'll explain what I do and perhaps you can adapt the process to macros.

If you are using the peoples' information strictly for the salutation of the message and the message doesn't change, you would open a recordset somehow (run a query) and step through the records. Pick up the information you want and concatinate it as necessary into a string . Use chr(10) and chr(13) where necessary to force a carriage return and line feed. Then concatinate with text of message.

This string is then placed as one of the parameters of a SendObject command, along with address etc.

People have indicated problems with the SendObject command and I have seen them when using a dial-up connection with small time intervals between transmissions of e-mail(15 seconds gave me an error every 9 or 10 records while 30 seconds gave only an occasional error. intervals of 60 seconds gave no errors.) When using this on a LAN with no timer interval, there were never any problems. Check back a week or so ago; there was a discussion about alternatives to the SendObject command.

 

[Delboy14]

thanks I will try to work on it

 

[sjswann]

The following VBA code in the "On Click" property behind a command button should send a message to each of the people in the table you have set up. It will help if you have an email field in the table.
Basically what it should do is supply the contents of the subject and the message to the variables defined, then each time the recordset is looped through, the recipients name will change and the email should be sent. There can be as mentioned above, problems with this command. You need to keep an eye out to make sure the recipients always get the message, but I'll leave that to you. GOOD LUCK.

Private Sub sendmail_Click()
On Error GoTo Err_sendmail_Click
Dim message As String
Dim recip As String
Dim subject As String
Dim newline As String
Dim DB As Database
Dim rs As Recordset
Dim strQuery As String

newline = Chr(13) & Chr(10)
subject = "Put Title of Subject Here "
message = "This is an automated message:" & newline & newline
message = message & "Addition To Message " & Forms![Main Form]!['can put stuff like this in'].Value & newline

Set DB = Currentdb

strQuery = "SELECT * FROM [YOUR PEOPLE TABLE]"
Set rs = DB.OpenRecordset(strQuery)
rs.MoveFirst
While Not rs.EOF
recip = rs.Fields("EMAIL ADDRESS FIELD&quot
DoCmd.SendObject acSendNoObject, , , recip, , , subject, message, False
rs.MoveNext
Wend

Exit_sendmail_Click:
Exit Sub

Err_sendmail_Click:
MsgBox Err.description
Resume Exit_sendmail_Click

End Sub

 

[Delboy14]

Great Thanks alot I will get open the post again if (..when) I haqve probs thanks

 

[RottPaws]

I know this may be a bit off-topic, but just thought I'd throw this in anyway . . . .

grnzbra mentioned using "...chr(10) and chr(13) where necessary..." and sjswann had a line in his code "newline = Chr(13) & Chr(10)"

Access recognizes the Visual Basic constant vbCrLf (VB Carriage Return Line Feed) which does this already.

For example:
strText="First line of text." & vbCrLf & "Second line of text"

Result:
First line of text.
Second line of text. _________
Rott Paws

...It's not a bug. It's an undocumented feature.

 

[Delboy14]

Hi,
I have managed to get further with my task. At present I have VBA code behind the on click property of a button which will send an email to the relevant people in a table.
The tables structure is

Practice  RCAs           Email
394	  Derek 	 dsmith@computing.dundee.ac.uk
356	  Derek 	 dsmith@computing.dundee.ac.uk
268	  Paula          pkw@computing.dundee.ac.uk
211	  Paula          pkw@computing.dundee.ac.uk	
132       Jean           jsmith@computing.dundee.ac.uk

I want to email the relevant practice numbers to each RCA e.g Derek would recieve an email with Practices 394 and 356.
(There are other details held on the practice but they dont seem relevant)

At present the code I am working operates fine with RCA's that have two practices, but does not handle practice with 1 or >2 practices. I have included the code below does anyone have an idea why it is not working?

While Not rs.EOF
    
           
    If (newRcaIndicator = True) Then 'Sets up email headings for new mails
        message = "Detailed RCA Report -" & rs.Fields("RCAs") & newline & newline & "Practice Name      Number"
        message = message & NewMessage
        NewMessage = ""
        newRcaIndicator = False
    End If
    
    If (Counter < 1) Then 'used 1st time as comparison cannot be used on first loop
        Counter = 1
        message = message & newline & newline & rs.Fields(&quot;Practice_Name&quot;) & &quot;   &quot; & rs.Fields(&quot;Practice_No&quot;)
        previousRCA = rs.Fields(&quot;RCAs&quot;)
    Else
            
            
                If (rs.Fields(&quot;RCAs&quot;) = previousRCA) Then
                
                    message = message & newline & newline & rs.Fields(&quot;Practice_Name&quot;) & &quot;   &quot; & rs.Fields(&quot;Practice_No&quot;)
                    previousRCA = rs.Fields(&quot;RCAs&quot;)
                    recipient = rs.Fields(&quot;Email&quot;)
                    previousEmail = recipient
                    practiceWithOneRCA = False
                    
                Else
                      DoCmd.SendObject acSendNoObject, , , recipient, , , subject, message, False
                      NewMessage = newline & newline & rs.Fields(&quot;Practice_Name&quot;) & &quot;   &quot; & rs.Fields(&quot;Practice_No&quot;)
                      newRcaIndicator = True
                      previousRCA = rs.Fields(&quot;RCAs&quot;)
                      practiceWithOneRCA = True
                      
                End If
            
    End If

    rs.MoveNext
Wend

I understand the code looks messy on the post, but any help would be great thanks Derek.

 

[Delboy14]

The problem is solved, I have included the working code below. My problems were, I was not sending the final email because once the end of the recordset was met it did not send a mail, but a message was still waiting to be sent. And I was picking up the wrong RCA name in one Practice RCA's, because the name was being set once the recordset had moved on.

While Not rs.EOF
    
           
    If (newRcaIndicator = True) Then
        message = NewMessage
        NewMessage = ""
        newRcaIndicator = False
    End If
    newRcaIndicator = False
    If (Counter < 1) Then
        Counter = 1
        message = message & newline & newline & rs.Fields(&quot;Practice_Name&quot;) & &quot;   &quot; & rs.Fields(&quot;Practice_No&quot;)
        previousRCA = rs.Fields(&quot;RCAs&quot;)
    Else
            
            
                If (rs.Fields(&quot;RCAs&quot;) = previousRCA) Then
                
                    message = message & newline & newline & rs.Fields(&quot;Practice_Name&quot;) & &quot;   &quot; & rs.Fields(&quot;Practice_No&quot;)
                    previousRCA = rs.Fields(&quot;RCAs&quot;)
                    recipient = rs.Fields(&quot;Email&quot;)
                    previousEmail = recipient
                    practiceWithOneRCA = False
                    NewMessage = &quot;&quot;
                Else
                      DoCmd.SendObject acSendNoObject, , , recipient, , , subject, message, False
                      NewMessage = &quot;Detailed RCA Report -&quot; & rs.Fields(&quot;RCAs&quot;) & newline & newline & &quot;Practice Name      Number&quot; & newline & newline & rs.Fields(&quot;Practice_Name&quot;) & &quot;   &quot; & rs.Fields(&quot;Practice_No&quot;)
                      newRcaIndicator = True
                      previousRCA = rs.Fields(&quot;RCAs&quot;)
                      practiceWithOneRCA = True
                      
                End If
            
    End If

    rs.MoveNext
Wend
DoCmd.SendObject acSendNoObject, , , recipient, , , subject, message, False