Sending email from Access query result VBA Outlok to different group of customers (1 Viewer)

[gfranco]

Hi,

I have a request which needs to group in a single email all of records that belongs a specific customer. Each customer might have multiple contacts(emails) to be sent. All of the records needs to be categorized and group by customer.
I just found this code and works except for the following:
1. Always asked me about security stuff.
2. I just can email one record at a time and I cannot group all of records in a single email for each customer.
Would you mind to help me guys about it. Thanks.

Private Sub Command0_Click()

Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String

Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("Query1", dbOpenSnapshot)


With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(7)) = False Then
sToName = .Fields(7)
sSubject = "SO #: " & .Fields(1)
sMessageBody = "Estimado cliente adjunto encontrara la informacion de la orden" & vbCrLf & _
"Cliente: " & .Fields(0) & vbCrLf & _
"Numero de Orden: " & .Fields(1) & vbCrLf & _
"Numero de Ship set: " & .Fields(2) & vbCrLf & _
"Numero de Linea de Orden: " & .Fields(3) & vbCrLf & _
"Pais de origen: " & .Fields(4) & vbCrLf & _
"Lugar de embarque: " & .Fields(5)
'"Field C: " & .Fields©

DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With

Set MyDb = Nothing
Set rsEmail = Nothing


End Sub

 

[steveboydon]

The chances are is that if your email application is corporate, then security settings will stop email being automated from an external application. (basically this stops viruses attacking your email client)

You could always do it in reverse.

Create the code in Outlook and pull the data from access. Its is pretty much the same interface. Just reference Access in the code window, ALTF11 to open up the editor.

Steve

 

[ghudson]

Do not use the DoCmd.SendObject method. Search for CreateObject("Outlook.Application") and you should find some examples on how to send emails that avoid the outlook security warnings.