Sending email through MS access

[talhaparvaiz@yahoo.com]

Hi,

I am new bee to Access and want to send emails to a multiple recipients through MS Access/Outlook. here are my requirements

• Recipient addresses are stored in a table (about 1000 of them)
• Message and subject will be the same for all the emails
• want to send one recipient per email
• want to attach word file to all the emails
• Want to send single email after every 5 minutes until all recipients receive it
• I want to suppress any pop-up
• my outlook currently have 2 accounts set up. want to use one of them

Thanks in advance for assistance

[June7]

This is a common topic and many code examples available.

Look at some of the threads referenced at bottom.

[pbaldy]

Note that this is cross posted.

https://www.excelguru.ca/content.php?184

[talhaparvaiz@yahoo.com]

This is a common topic and many code examples available.

Look at some of the threads referenced at bottom.

cool, I am able to send basic email with the code below.


Here is what I am having challenges with:


1) email is not adding attachments
2) rather than sending emails to fixed addresses, I want to send it to recipients saved in the table "Emails_to_send"


can you please assist








Option Compare Database


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


' 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("To@email.com")
objOutlookRecip.Type = olTo


' Add the CC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("CC@email.com")
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 Sub

[Welshgasman]

Note that this is cross posted.

https://www.excelguru.ca/content.php?184

Also reported here https://www.access-programmers.co.uk/forums/threads/sending-emails-to-multiple-recipients-via-ms-access-outlook.311468

[June7]

I never used Attachment object. Simple example, dress it up as much as you want:

With objOutlookMsg
        .To = "some email"
        ''.cc = ""
        ''.bcc = ""
        .Attachments.Add "filepath\filename"
        .Subject = "Test"
        .Display
        .body = "Test " & vbCrLf & "multi-line" & vbCrLf & " message."
    '    .Send
End With

[talhaparvaiz@yahoo.com]

I never used Attachment object. Simple example, dress it up as much as you want:

With objOutlookMsg
        .To = "some email"
        ''.cc = ""
        ''.bcc = ""
        .Attachments.Add "filepath\filename"
        .Subject = "Test"
        .Display
        .body = "Test " & vbCrLf & "multi-line" & vbCrLf & " message."
    '    .Send
End With

Here is what I did

1) modified code as below and ran it, resulting in an error message "compile error: user defined type not defined"

Option Compare Database




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




' 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("To@email.com")
objOutlookRecip.Type = olTo




' Add the CC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("CC@email.com")
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("W:\My Documents\Customers.txt")
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 Sub





2) found a solution to this problem @ the following site that resulted in another error


https://www.cimaware.com/expert-zone...pe-not-defined



"Name conflicts with the existing module, project or object library"

I have my visual steps with this note. Please advise

Thanks





Re. Cross posted... may I please know what am I doing wrong?

Thanks

[pbaldy]

Re. Cross posted... may I please know what am I doing wrong?

Did you read the link?

[talhaparvaiz@yahoo.com]

Did you read the link?

Sure, I wasn't aware of this rule.
so that I have it right, it's okay to post same (or similar) post on multiple forums but we need to put the cross references. Please let me know

Thanks

[pbaldy]

Yes, it's okay to do as long as you let people know. It's very frustrating to put in a bunch of work to answer a question only to find it was answered earlier on another site.

[talhaparvaiz@yahoo.com]

Here is what I did

1) modified code as below and ran it, resulting in an error message "compile error: user defined type not defined"

Option Compare Database




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




' 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("To@email.com")
objOutlookRecip.Type = olTo




' Add the CC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("CC@email.com")
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("W:\My Documents\Customers.txt")
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 Sub





2) found a solution to this problem @ the following site that resulted in another error


https://www.cimaware.com/expert-zone...pe-not-defined



"Name conflicts with the existing module, project or object library"

I have my visual steps with this note. Please advise

Thanks





Re. Cross posted... may I please know what am I doing wrong?

Thanks

Any suggestions on my issues?

[Gicu]

Here is a VBA function I use to send Outlook messages, just add it to a standard module:

Function vcSendEmail_Outlook_With_Attachment(sSubject As String, sTo As String, Optional sCC As String, Optional sBcc As String, Optional sAttachment As String, Optional sBody As String)
    Dim OutApp As Object
    Dim OutMail As Object
 
  
    Set OutApp = CreateObject("Outlook.Application")
    
    
    OutApp.Session.Logon
  
 
    Set OutMail = OutApp.CreateItem(0)
 
    OutMail.To = sTo
    If sCC <> "" Then OutMail.CC = sCC
    If sBcc <> "" Then OutMail.BCC = sBcc
    OutMail.Subject = sSubject
    If sBody <> "" Then OutMail.HTMLBody = sBody


    OutMail.Attachments.Add (sAttachment)


    OutMail.Display  'Send | Display
    Set OutMail = Nothing
    Set OutApp = Nothing
End Function

To use it just call it from a loop and supply the required/optional arguments as needed.

Based on your original requirements I think it would be much easier/faster to concatenate multiple email addresses into one semi-colon separated string and use that for the bCC of the new message, as it will appear to the recipients that they were the only one being included.

Cheers,

[talhaparvaiz@yahoo.com]

Here is a VBA function I use to send Outlook messages, just add it to a standard module:

Function vcSendEmail_Outlook_With_Attachment(sSubject As String, sTo As String, Optional sCC As String, Optional sBcc As String, Optional sAttachment As String, Optional sBody As String)
    Dim OutApp As Object
    Dim OutMail As Object
 
  
    Set OutApp = CreateObject("Outlook.Application")
    
    
    OutApp.Session.Logon
  
 
    Set OutMail = OutApp.CreateItem(0)
 
    OutMail.To = sTo
    If sCC <> "" Then OutMail.CC = sCC
    If sBcc <> "" Then OutMail.BCC = sBcc
    OutMail.Subject = sSubject
    If sBody <> "" Then OutMail.HTMLBody = sBody


    OutMail.Attachments.Add (sAttachment)


    OutMail.Display  'Send | Display
    Set OutMail = Nothing
    Set OutApp = Nothing
End Function

To use it just call it from a loop and supply the required/optional arguments as needed.

Based on your original requirements I think it would be much easier/faster to concatenate multiple email addresses into one semi-colon separated string and use that for the bCC of the new message, as it will appear to the recipients that they were the only one being included.

Cheers,

Here is what I did

1) created a sub

Option Compare Database


Sub vcSendEmail_Outlook_With_Attachment(sSubject As String, sTo As String, Optional sCC As String, Optional sBcc As String, Optional sAttachment As String, Optional sBody As String)
Dim OutApp As Object
Dim OutMail As Object


Set OutApp = CreateObject("Outlook.Application")


OutApp.Session.Logon


Set OutMail = OutApp.CreateItem(0)

OutMail.To = sTo
If sCC <> "" Then OutMail.CC = sCC
If sBcc <> "" Then OutMail.BCC = sBcc
OutMail.Subject = sSubject
If sBody <> "" Then OutMail.HTMLBody = sBody




OutMail.Attachments.Add (sAttachment)




OutMail.Display 'Send | Display
Set OutMail = Nothing
Set OutApp = Nothing
End Sub


2) calling it from the form button click

Option Compare Database


Private Sub Command0_Click()




vcSendEmail_Outlook_With_Attachment "Seeking BA opportunities", "info@test.com", "", "x", "\\fa\fahome\user\Desktop\personal\file.docx", ""




End Sub




Questions:
I have my email addresses (about 1000) in a table_A, column name Email
Q1 - Rather than using fixed email address, how can I get the email address from the table and pass it to the function
Q2 - how can I put, let's say 20 email addresses, per email in the BCC field? believe they should be in the format such as A@email.com, B@email.com...
Q3 - the above code is creating a new email with the data populated, but I am still required to press the send button. This is a good option for testing purposes but I want to send the email without any popups.

Please assist.

Thanks again for your assistance

[June7]

Please post code between CODE tags to retain indentation and readability.

Need to open a recordset object of email addresses. Then loop the recordset. The loop will either send multiple emails (each for one address) or build a concatenated string of addresses which will be used outside the loop to send one email with multiple addresses.

Here is example of the latter https://www.vba-market.com/vba-send-...le-recipients/

[talhaparvaiz@yahoo.com]

Please post code between CODE tags to retain indentation and readability.

Need to open a recordset object of email addresses. Then loop the recordset. The loop will either send multiple emails (each for one address) or build a concatenated string of addresses which will be used outside the loop to send one email with multiple addresses.

Here is example of the latter https://www.vba-market.com/vba-send-...le-recipients/

To keep it simple, I tried to extract 10 email addresses from the DB and send it to individual recipients. Here is the code that I used but getting an error message


Calling function on the click of the button:

Option Compare Database

Sub Command0_Click()


Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
Dim i As Integer
Dim value As Variant


initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
objRecordset.Open ("tbl_Email")




Dim i As Integer
For i = 1 To 10 'sending 10 emails individually

vcSendEmail_Outlook_With_Attachment "Subject", objRecordset.Fields.Item(1).value, "", "", "W:\My Documents\Customers.txt", ""




objRecordset.MoveNext


Next i
End Sub

function:

Option Compare Database

Sub vcSendEmail_Outlook_With_Attachment(sSubject As String, sTo As String, Optional sCC As String, Optional sBcc As String, Optional sAttachment As String, Optional sBody As String)
    Dim OutApp As Object
    Dim OutMail As Object
 
  
    Set OutApp = CreateObject("Outlook.Application")
    
    
    OutApp.Session.Logon
  
 
    Set OutMail = OutApp.CreateItem(0)
 
    OutMail.To = sTo
    If sCC <> "" Then OutMail.CC = sCC
    If sBcc <> "" Then OutMail.BCC = sBcc
    OutMail.Subject = sSubject
    If sBody <> "" Then OutMail.HTMLBody = sBody




    OutMail.Attachments.Add (sAttachment)




    OutMail.Display  'Send | Display
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

error message





Thanks