X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Send Multiple Emails Via Gmail With Separate Attachments For Each Email, Any Version    
post Jul 26 2019, 09:21 AM

Posts: 5,515
Joined: 2-November 04
From: Downey, CA

This isn't an Outlook question per se, I have a list of 100+ employees that I need to send an individual e-mail to each employee with 2 .pdfs attached per e-mail, per employee - I can take the time to set up a table with all e-mail attachments and references if necessary - I couldn't find the answer on the 'net, especially for GMAIL - I have used code before to send out individual e-mails, but not this - I'm thinking of setting up an excel worksheet with each employee's email in col A, one of the .pdf's in col B and the other (which actually was an excel file before being converted to .pdf, and saying something like attachment (the value in B2) - I've done something similar to this before, but never like this - before, I had to send a daily e-mail to one person, and several people were copied on the e-mail, but they were hard-coded in my VBA code, and this was using access

Any ideas ?

Thanks in advance....

Go to the top of the page
post Jul 26 2019, 11:44 AM

Posts: 2,541
Joined: 4-February 07
From: USA, Florida, Delray Beach

  1. This is a relatively simple matter if Outlook is your E-Mail Client.
  2. Let's assume that you have the following Data in Sheet1 with Column A being the Employee's Name, Column B being his/her E-Mail Address, Column C the first File to attach, and Column D being the second File to attach.
    John Doe    JDoe@Aol.com      C:\Test\Test2.pdf     C:\Test\Test1.pdf
    Peter Pan   PPan@Comcast.net  C:\Test\Test3.pdf     C:\Test\Test4.pdf
    Fred Lee    FLee@Yahoo.com    C:\Test\Test6.pdf     C:\Test\Test5.pdf
  3. The following Code will process each Employee sending him/her a Name related E-Mail with the appropriate Attachments. The Code assumes that the Names in Column A are continuous with no 'Gaps.
  4. The following Code uses Early Binding but you could also Late Bind if preferred.
    Dim oLook  As Outlook.Application
    Dim oMail  As Outlook.MailItem
    Dim olns As Outlook.Namespace
    Dim sht As Excel.Worksheet
    Dim intRow As Integer

    Set sht = ActiveWorkbook.Worksheets("Sheet1")
    Set oLook = New Outlook.Application
    Set oMail = oLook.CreateItem(olMailItem)
    Set olns = oLook.GetNamespace("MAPI")

    intRow = 1      '1st Row with Data in it

    With sht
      Do While .Cells(intRow, "A") <> ""
       Set oMail = oLook.CreateItem(olMailItem)
        oMail.Subject = "Attached Files for Employee " & .Cells(intRow, "A")
        oMail.Body = "Please read the following Documentation which explains in detail " & _
                     "your new Medical Benefits Package"
        oMail.To = .Cells(intRow, "B")
        oMail.Attachments.Add CStr(.Cells(intRow, "C"))
        oMail.Attachments.Add CStr(.Cells(intRow, "D"))
            intRow = intRow + 1
    End With

    Set oLook = Nothing
    Set oMail = Nothing
    Set olns = Nothing
  5. If GMail is your E-Mail Client then I do believe that matters would be more difficult.
Go to the top of the page
post Jul 26 2019, 01:55 PM

Posts: 5,515
Joined: 2-November 04
From: Downey, CA

Dezii, thank you SO much! I did mention this was to be done in GMAIL, but this is great to know - I may even be able to tell them - "Hey, here's how to do it (using Outlook), but I don't know how to do this in GMAIL" This is so crazy the company I'm working for uses freakin' GMAIL ! I hate it with a passion ! Ugh!

One more thing - I have it somewhere in my notes, but do you know off-hand how to print a list of files in a folder - that's really easy, but if you know off the top of your head... like even using DOS commands...

Thanks Again !!!!! cool.gif

Go to the top of the page
post Jul 26 2019, 02:24 PM

Posts: 2,541
Joined: 4-February 07
From: USA, Florida, Delray Beach

  1. Here is a very simple example on how you can Print all Files (*.*) within a single Folder:
    Dim strFile As String
    Const conPATH_TO_FILES = "C:\Windows\Media\*.*"

    strFile = Dir$(conPATH_TO_FILES)

    Debug.Print "Files for: " & conPATH_TO_FILES
    Debug.Print "-----------------------------------"

    Do While strFile <> ""
      Debug.Print strFile
        strFile = Dir
  2. OUTPUT (partial):
    Files for: C:\Windows\Media\*.*
    Speech Disambiguation.wav
    Speech Misrecognition.wav
    Speech Off.wav
    Speech On.wav
    Speech Sleep.wav
    Windows Balloon.wav
    Windows Battery Critical.wav
    Windows Battery Low.wav
    Windows Critical Stop.wav
    Windows Default.wav
    Windows Ding.wav
    Windows Error.wav
    Windows Exclamation.wav
    Windows Feed Discovered.wav
    Windows Hardware Fail.wav
    Windows Hardware Insert.wav
    Windows Hardware Remove.wav
    Windows Information Bar.wav
    Windows Logoff Sound.wav
    Windows Logon Sound.wav
    Windows Menu Command.wav
    Windows Minimize.wav
    Windows Navigation Start.wav
    Windows Notify.wav
    Windows Pop-up Blocked.wav
    Windows Print complete.wav
    Windows Recycle.wav
    Windows Restore.wav
    Windows Ringin.wav
    Windows Ringout.wav
    Windows Shutdown.wav
    Windows Startup.wav
    Windows User Account Control.wav
  3. Here is a NOT-SO-SIMPLE example on how you can use CDO to send an E-Mail to a Recipient using GMail as your E-Mail Client. If you can get the necessary Parameters (SMTP Server Name/ Port#, etc.) to send a single E-Mail, I'm fairly sure that we can expand the Code to send to Multiple Recipients with Attachments.
    Public Sub SendGmail()
    'Creating a CDO object, set a Reference to the
    'Microsoft CDO for Windows 2000 Library
    Dim Mail As CDO.Message
    Set Mail = New CDO.Message

    'Enable SSL Authentication
    Mail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/CDO/configuration/smtpusessl") = True

    'Make SMTP authentication Enabled=True (1)
    Mail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/CDO/configuration/smtpauthenticate") = 1

    'Set the SMTP Server and Port Details
    'Get these details from the Settings Page of your Gmail Account
    Mail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/CDO/configuration/smtpserver") = _
    Mail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/CDO/configuration/smtpserverport") = 25
    Mail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/CDO/configuration/sendusing") = 2

    'Set your credentials of your Gmail Account
    Mail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/CDO/configuration/sendusername") = _
    Mail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/CDO/configuration/sendpassword") = _

    'Update the Configuration Fields

    'Set All Email Properties
    With Mail
      .Subject = "Write subject here"
      .From = "username@gmail.com"
      .To = "user1@gmail.com;user4@gmail.com"
      .CC = "user2@gmail.com"
      .BCC = "user3@gmail.com"
      .textbody = "write your mail here"
      .AddAttachment ("Folder Address") 'To attach Documents in mail
    End With

    'to Send the mail
    End Sub
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    20th August 2019 - 06:36 PM