send email one at a time

[waltr1122]

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

 

[Macropod]

This is quite straightforward using mailmerge. See: https://support.office.com/en-us/ar...messages-0f123521-20ce-4aa8-8b62-ac211dedefa4

 

[Worf]

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.

 ' 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("[URL]http://schemas.microsoft.com/mapi/proptag/0x0E04001E[/URL]") & 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

 

[waltr1122]

This is quite straightforward using mailmerge. See: https://support.office.com/en-us/ar...messages-0f123521-20ce-4aa8-8b62-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

 

[Worf]

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?

 

[waltr1122]

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

 

[Worf]

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.

' 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

 

[Worf]

Including the email section:

' 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

 

[Macropod]

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.

 

[waltr1122]

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