Solved Sending email using outlook

[donkey9972]

I am fairly new to microsoft access and I need a lot of help. I made a very basic database, I have a table and 2 forms. One form is letting me input some basic information that I am wanting to store, like customer names, phone numbers, addresses and email addresses. I then have a second form and I put a button on there and using the code I found (which took me forever to figure out where it went) which is this:

Sub sendOutlookEmail()
Dim oApp As Outlook.Application
Dim oMail As MailItem
Set oApp = CreateObject("Outlook.application")

Set oMail = oApp.CreateItem(olMailItem)
oMail.Body = "Body of the email"
oMail.Subject = "Test Subject"
oMail.To = "Someone@somewhere.com"
oMail.Send
Set oMail = Nothing
Set oApp = Nothing

End Sub

So I just have a few questions I was hoping to get some help with. The first one is how can I send an attachment with this code, or is that not possible? The next question I have is this, is there anyway to send a bulk email using all of my customer email addresses stored in my table? I hope someone can help me with this as I am lost, and being it took me nearly 2 days to figure out where to put the code I found, I might need a lot of explanation to figure out where to put anything I hope to learn on here.

 

[Gasman]

Adding attachments is asked regularly. Search on this site, the search process is very good.

Here is my output from such a search. You should just be able to copy the code and amend to suit.

Search results for query: add attachments

 

[arnelgp]

i made a demo, sometimes ago using Outlook email.

 

[donkey9972]

Wow that is a nice database. Way to advanced for me to understand how all that works with those modules and things. I appreciate you attaching that database, I was just hoping to use the regular old outlook program to send my emails out, but I did not want to make my email address book cluttered with all of my customer email addresses which is why I started to store them in microsoft access. I have a small hometown business and I just use my personal email in outlook for both customer mailings and also personal things. About once or twice a month I email all of them some flyers. I was just hoping this would be easier as a bulk email. I was just wanting to click a button and it email everyone on my list and attach my flyer to it. Gasman, I am looking down that search result, but I am so lost on how to use any of that. I will need someone to walk me through where to put anything I can find in there. I keep seeing these titles with solved in them but I do not know which are the solved parts or if they would work for what I am trying to figure out.

 

[donkey9972]

Yes, I found some code that works.

Private Sub Command0_Click()

    Dim strEMail As String
    Dim oOutlook As Object
    Dim oMail As Object
    Dim strAddr As String
    Dim MyDB As DAO.Database
    Dim rstEMail As DAO.Recordset
  
    Set oOutlook = CreateObject("Outlook.Application")
    Set oMail = oOutlook.CreateItem(0)
  
    'Retrieve all E-Mail Addressess in tblEMailAddress
    Set MyDB = CurrentDb
    Set rstEMail = MyDB.OpenRecordset("Select * From Table1", dbOpenSnapshot, dbOpenForwardOnly)
  
    With rstEMail
      Do While Not .EOF
        'Build the Recipients String
        strEMail = strEMail & ![EMail] & ";"
          .MoveNext
      Loop
    End With
    '--------------------------------------------------
  
    With oMail
      .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
      .Body = "Test E-Mail to Multiple Recipients"
      .Subject = "Yada, Yada, Yada"
'        .Send
        .display
    .Attachments.Add ("Pathtomyfile")
  
    End With
  
    Set oMail = Nothing
    Set oOutlook = Nothing
  
    rstEMail.Close
    Set rstEMail = Nothing

End Sub

This works really really nice. I just thought everyone would like to know.

 

[bastanu]

Thanks for sharing, I would suggest you tweak your code to use the Bcc instead of To:
.Bcc = Left$(strEMail, Len(strEMail) - 1) 'Remove Trailing ;
That way each individual on the list would get the email but without seeing all the others (I know I would be upset if one of my contacts would share my email with all his clients).

Cheers,

 

[GPGeorge]

Yes, I found some code that works.

Spoiler: Etc.

Private Sub Command0_Click()

    Dim strEMail As String
    Dim oOutlook As Object
    Dim oMail As Object
    Dim strAddr As String
    Dim MyDB As DAO.Database
    Dim rstEMail As DAO.Recordset
 
    Set oOutlook = CreateObject("Outlook.Application")
    Set oMail = oOutlook.CreateItem(0)
 
    'Retrieve all E-Mail Addressess in tblEMailAddress
    Set MyDB = CurrentDb
    Set rstEMail = MyDB.OpenRecordset("Select * From Table1", dbOpenSnapshot, dbOpenForwardOnly)
 
    With rstEMail
      Do While Not .EOF
        'Build the Recipients String
        strEMail = strEMail & ![EMail] & ";"
          .MoveNext
      Loop
    End With
    '--------------------------------------------------
 
    With oMail
      .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
      .Body = "Test E-Mail to Multiple Recipients"
      .Subject = "Yada, Yada, Yada"
'        .Send
        .display
    .Attachments.Add ("Pathtomyfile")
 
    End With
 
    Set oMail = Nothing
    Set oOutlook = Nothing
 
    rstEMail.Close
    Set rstEMail = Nothing

End Sub

This works really really nice. I just thought everyone would like to know.

You probably also want to exercise caution in "spamming" your customers. You know how people feel about that. Also, some internet providers frown on massive email campaigns, so make sure you stay within guidelines that won't get you blacklisted.

 

[donkey9972]

bastanu, I will try to set it up to use the bcc it is a great suggestion. GPGeorge, thank you for the concern, I have my customers permission to send them the emails that I am. Also I will check with my ISP and verify their guidelines. Thank you both.