Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: send email one at a time

  1. #1
    New Member
    Join Date
    Aug 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default send email one at a time

    Hi all, been some time since I have had need of your excellent advice/help. I need to send out a questionare to approximately 50 people. Each one will contain their individual personal information that I need to get back from them if it needs to be updated. I would like the excel spreadsheet to do all this or at worst have it done on word (mailmerge). I remember being able to do it all in excel many years ago but it has been so long that I haven't got a clue how to do it. Heck I just tried to do a somple VLOOKUP and kept screwing it up.

    So I have names and addresses and email addresses. I will need the "letter" pick up the personal information and have it then send itself out using outlook or Gmail etc. Then it needs to loop back to the next email address and do them each one by one until the end of the list. Is this a pipe dream or is it possible? Also I'm not sure if the email addresses are any good. Is there a way to have it log any errors? Or let me know if the emails fail to get delivered.

    Walt

  2. #2
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    2,939
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: send email one at a time

    This is quite straightforward using mailmerge. See: https://support.office.com/en-us/art...2-ac211dedefa4
    Cheers
    Paul Edstein
    [MS MVP - Word]

  3. #3
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,427
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: send email one at a time

    Or let me know if the emails fail to get delivered.
    This code defines an Outlook folder, filters by date, finds the non-delivery reports and extracts the mail addresses. Worked for me.



    Code:
     ' Outlook module
    Sub DateComparison()
    Dim colItems As items, rst As items, datStartUTC As Date, datEndUTC As Date, _
    i%, oMail As MailItem, oPA As PropertyAccessor, fm$
    fm = ""
    'Set colItems = Application.Session.GetDefaultFolder(olFolderInbox).items       ' or this one
    Set colItems = Application.Session.GetDefaultFolder(olFolderDeletedItems).items ' desired folder
    Set oMail = Application.CreateItem(olMailItem)
    Set oPA = oMail.PropertyAccessor
    datStartUTC = oPA.LocalTimeToUTC(Date)                                          ' today
    datEndUTC = oPA.LocalTimeToUTC(DateAdd("d", 1, Date))
    Set rst = colItems.Restrict("@SQL=" & "%today(" & AddQuotes("urn:schemas:httpmail:datereceived") & ")%")
    For i = 1 To rst.Count
        If (rst.item(i).MessageClass = "REPORT.IPM.Note.NDR") Then _
        fm = fm & rst.item(i).PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E04001E") & vbLf
    Next
    MsgBox fm, vbExclamation, "Failed"
    Set oMail = Nothing
    End Sub
    
    Public Function AddQuotes$(ByVal SchemaName$)
        On Error Resume Next
        AddQuotes = Chr(34) & SchemaName & Chr(34)
        On Error GoTo 0
    End Function
    Last edited by Worf; Sep 13th, 2018 at 12:20 PM.
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  4. #4
    New Member
    Join Date
    Aug 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: send email one at a time

    Quote Originally Posted by Macropod View Post
    This is quite straightforward using mailmerge. See: https://support.office.com/en-us/art...2-ac211dedefa4
    Hi Macropod, I was having trouble seeing how that link would work when I looked at it before I came here. Maybe I need to give it a second look. In the past I had it done mostly in the spreadheet. I wish I had kept copies of how it was done but that was years ago and you guys did most of the heavy lifting then. The spreadsheet would make one copy at a time and then create a tab to keep it on hand for proof. Then it would create a new letter with the new information send it and keep a copy of it and so on and so on. If the email address was bad then I could print out the letter and send by snail mail.

    Thanks Worf, I will keep this and see if I can understand/use it.

    Walt

  5. #5
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,427
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: send email one at a time

    Hi Walt

    It is possible to have an Excel workbook that performs one of these tasks, using VBA:


    • Execute a mail merge.
    • Create simple Word letters and email them using Outlook.


    Which option do you prefer?
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  6. #6
    New Member
    Join Date
    Aug 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: send email one at a time

    Hi Worf, I think the best choice would be to create simple Word letters linked to excel data and then email them using Outlook. I know I said something about using Excel for everything but using word would probably make more sense. Thanks for offering to help.


    Walt


  7. #7
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,427
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: send email one at a time

    The following example uses a Word template with two rich text controls, added at Ribbon>Developer>Controls.
    Data is extracted from the worksheet and inserted into the newly created Word documents. I will be back later with the Outlook part.

    Code:
    ' Excel module
    Sub ccc()
    Dim wdoc As Document, gd$, wapp As Word.Application, c%, i%
    gd = GetDesktop
    On Error Resume Next
    Set wapp = GetObject(, "Word.Application")
    On Error GoTo 0
    If wapp Is Nothing Then Set wapp = New Word.Application
    c = 0
    For i = 1 To Range("a" & Rows.Count).End(xlUp).Row
        c = c + 1
        If c > 10 Then Exit Sub
        Set wdoc = wapp.Documents.Add(Template:=gd & "\my_letter.dotx", _
        NewTemplate:=False, DocumentType:=0)
        wdoc.ContentControls(1).Range.Text = Cells(i, 1)
        wdoc.ContentControls(2).Range.Text = Cells(i, 2)
        wdoc.SaveAs2 gd & "\letter" & i & ".docx", 16
        wdoc.Close True
    Next
    End Sub
    
    
    Function GetDesktop$()
    Dim oWSHShell As Object
    Set oWSHShell = CreateObject("WScript.Shell")
    GetDesktop = oWSHShell.SpecialFolders("Desktop")
    Set oWSHShell = Nothing
    End Function
    Last edited by Worf; Sep 18th, 2018 at 08:49 PM.
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  8. #8
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,427
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: send email one at a time

    Including the email section:

    Code:
    ' Excel module
    Sub ccc()
    Dim wdoc As Document, gd$, wapp As Word.Application, c%, i%, _
    olapp As Outlook.Application, mi As MailItem
    gd = GetDesktop
    On Error Resume Next
    Set wapp = GetObject(, "Word.Application")
    Set olapp = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If wapp Is Nothing Then Set wapp = New Word.Application
    If olapp Is Nothing Then Set olapp = New Outlook.Application
    wapp.Visible = True
    c = 0
    For i = 1 To Range("a" & Rows.Count).End(xlUp).Row
        c = c + 1
        If c > 10 Then Exit Sub
        Set wdoc = wapp.Documents.Add(Template:=gd & "\my_letter.dotx", _
        NewTemplate:=False, DocumentType:=0)
        wdoc.ContentControls(1).Range.Text = Cells(i, 1)
        wdoc.ContentControls(2).Range.Text = Cells(i, 2)
        wdoc.SaveAs2 gd & "\letter" & i & ".docx", 16
        wdoc.Close True
        Set mi = olapp.CreateItem(0)
        mi.Attachments.Add gd & "\letter" & i & ".docx"
        mi.To = Cells(i, 3)
        mi.Body = "See attached file."
        mi.Display
    Next
    End Sub
    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  9. #9
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    2,939
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: send email one at a time

    Quote Originally Posted by waltr1122 View Post
    Hi Macropod, I was having trouble seeing how that link would work when I looked at it before I came here. Maybe I need to give it a second look.
    As good as Worf's code is, using Word to drive a mailmerge to email is significantly simpler, easier to maintain, and no code is required... If there is any advantage in Worf's approach, it's that the letter goes as an attachment (if that's what you want) whereas a mailmerge sends it as the email body.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  10. #10
    New Member
    Join Date
    Aug 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: send email one at a time

    Hi guys, thanks to you both. I will look into both options. I think that the straight forward approach of using Word would probably work out best for the people I'm trying to help get this going. They just need to for some information mailings ( newsletter, email update requests, and fund raising/thank you emails, etc.). I wanted to try to make the emails more interactive using the excel spreadsheet with IF statements getting data from spreadsheets. Probably to hard for them to make changes if or when they become necessary. I think I will just have them do some form letters in Word and show them how to get the data from the spreadsheet.

    thanks again for showing me my options and giving me some thoughts on the process.


    Walt

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •