Sending email from outlook, how to get list of "to" addr's from table

[joshs1100]

When using DoCmd.SendObject, how can I take a table with a list of email addresses, and use it in the expression to complete the "to" parameter with a semicolon separated list?

Thanks!

 

[VBAjedi]

Just concatenate the addresses into a string before the DoCmd.SendObject line. Something like (pretends your data is in range A5:A20):

For x = 5 to 20 
  a = Range("A" & x).Value
  If Len(a) > 0 Then
    ToList = ToList & a & ";"
  End If
Next x
' Now strip off trailing semicolon
ToList = Left(ToList, 1, (Len(ToList) - 1))

Hope that helps!


VBAjedi

 

[joshs1100]

I think I may have posted this in the wrong forum. I'm using an access table to store the addresses, and using a command button on the form to send the email using outlook.

 

[denosaur]

Joshs1100,

Try this example, which sends an email to users in a table:

You may have to register the following references (Tools...References...):
1) Microsoft ActiveX Data Object 2.5 Library
2) Microsoft CDO For Exchange 2000 Library.

'------------------------------------------------------------
' Send Sales Report Email Reminders
'
'------------------------------------------------------------
Function SendSalesReminders()
On Error GoTo err_routine

Dim dbs As Database
Dim rst As Recordset
Dim strWhen As String
Dim iConf As New CDO.Configuration
Dim Flds As ADODB.Fields
Set Flds = iConf.Fields

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM qryClientsConverting ORDER BY AccountManagerName")

Do While Not rst.EOF
strAMFirstName = rst!FirstName
strAMName = rst!AccountManagerName
strClient = rst!ClientName
strEmail = rst!AccountManagerEmailAddress
' The full field name strings are used below to illustrate this process.
' The CDO for Exchange 2000 type library contains string Modules
' that provide these values as named constants.
' Use these module constants to avoid typos and so on.

Flds("

http://schemas.microsoft.com/cdo/configuration/smtpserver")

= "MAIL01" ' your email server IP address
Flds("

http://schemas.microsoft.com/cdo/configuration/smtpserverport")

= 25 ' default port
Flds("

http://schemas.microsoft.com/cdo/configuration/sendusing")

= cdoSendUsingPort ' CdoSendUsing enum value = 2
Flds("

http://schemas.microsoft.com/cdo/configuration/smtpaccountname")

= "SMTP Account Name" ' your account name
Flds("

http://schemas.microsoft.com/cdo/configuration/sendemailaddress")

= "myemailaddress@mydomain.com" ' your email address
Flds("

http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")

= cdoBasic

' IMPORTANT: Storing user names and passwords inside source code
' can lead to security vulnerabilities in your software. Do not
' store user names and passwords in your production code.

Flds("

http://schemas.microsoft.com/cdo/configuration/sendusername")

= "uid" 'domain\username
' this is your user name that is used to log on to your desktop
Flds("

http://schemas.microsoft.com/cdo/configuration/sendpassword")

= "pwd" ' password
Flds.Update ' this is your password that is used to log on to your desktop.

Dim iMsg As New CDO.Message
Set iMsg.Configuration = iConf
With iMsg
.To = strEmail
.From = "myemailaddress@mydomain.com"
.Subject = strClient & " Sales Report due on " & Format(Now(), "mm/dd/yyyy")
.TextBody = strAMFirstName & "," & vbLf & vbLf & "Please prepare and send me the " & _
strClient & " Sales Report that is due on the 15th. " & _
"Please let me know if you have any questions or cannot send me the report today." & _
vbLf & vbLf & "Thanks"
.Send
End With

rst.MoveNext
Loop
Set iMsg = Nothing

Exit Function

exit_routine:
Exit Function

err_routine:

MsgBox Err.Number & ": " & Err.Description
Resume exit_routine

End Function


Hope this helps.