Adding names to a mail using Outlook

[Smudger1803]

OK - I'm really rubbish at VBA (That pretty much sets the bar)

I am trying to send an attachment using VBA by calling up Outlook but i can't get it to use the list of names in a table.

Here is the script i am using which i 'borrowed' from one of your links but i can't get it to work how i want


Function SendMessage(Optional AttachmentPath)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

Set dbs = CurrentDb
Set rstEmailDets = dbs.OpenRecordset("SELECT To FROM Tbl_9999_E_Mail;")

This bit in red i added to show what i am trying to call up


' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("rstEmailDets")
objOutlookRecip.Type = olTo
' Add the CC recipient(s) to the message.
'Set objOutlookRecip = .Recipients.Add("Andrew Fuller")
'objOutlookRecip.Type = olCC
' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "Last test - I promise." & vbCrLf & vbCrLf
'.Importance = olImportanceHigh 'High importance
' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If
' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Function


Thanks in advance anyone

 

[noboffinme]

Re: Adding names to a mailusing Outlook

Hi Smudger1803

Try this code which is based on a query you create that gives the e-mail addresses for the people you want to send to.

You can add the subject line, the body & add an attachment also if you need.

---------------------------------

Option Explicit
Dim OutApp As Outlook.Application 'Object
Dim OutMail As Object
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strEmail As String

Private Sub Multiple_E_mail_Click()
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
Set cn = CurrentProject.Connection


Set rs = New ADODB.Recordset
On Error Resume Next

rs.Open "your_query_name_here", cn
With rs
Do While Not .EOF
strEmail = strEmail & .Fields("Contact E-mail") & ";"
.MoveNext
Loop
.Close
End With
With OutMail
.Display 'or use .Send
.To = strEmail
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.body = "Hello"
'Add any other files also like this
'.Attachments.Add ("C:\test.txt")

End With
strEmail = Left(strEmail, Len(strEmail) - 1)
Set OutApp = Nothing
Set rs = Nothing
Set OutMail = Nothing
Set cn = Nothing
End Sub

 

[Smudger1803]

Re: Adding names to a mailusing Outlook

Hi Noboffinme,

Thank you very much for this....You have done my office a great service - I was turning into a real 'grouch' trying to work this through.
Have a great Christmas break!

 

[noboffinme]

Re: Adding names to a mailusing Outlook

No problem

 

[boblarson]

By the way, be careful about using Access Reserved Words as field or object names. TO is an Access Reserved Word so you should not use that exactly as a field or object name. But if you do then you need to enclose it in square brackets so that Access knows you want a field name and are not trying to use it as a reserved word.

See here for a comprehensive list of reserved words (and hence not to use for field or object names).