Excel / Word / E-mail

[stapuff]

I am looking for some suggestions on a project I am looking to do for my in-laws. They are in the golf clothing business and really do everything manually.

The project is to help send out e-mails automatically to their customer base letting them know when they are having a "samples" sale.

I have an excel spreadsheet that column A is the persons title (Mr., Ms., Mrs.,etc)
Column B is the first name, Column C is the last name, and column D is the E-mail address.

In Word I have a letter already drafted without the title or name of the person.

The end result would be a macro that pulls the title and name from Excel (row2), sends it to Word, then E-mails it, then row3,row4,row5, etc.

The e-mail is Outlook.

I request suggestions because I have never used Word (outside of typing a quick letter).

Is this possible?

Thanks in advance for your thoughts,

Kurt

[Ken Puls]

Hi Kurt,

In Word 2003, you can mail merge right into email messages. It may save you a bunch of time trying to build code if it's realtively easy.

I've never tried using it, but it is something you may want to try. :)

Come to think of it, you could always record the mail merge steps in a macro, (in Word,) then you wouldn't have to go through it every time. :dunno

[sandam]

Have you looked at using a mail-merge, because from you're explanation that is exactly what you have set up.

Save your original letter somewhere as a backup :) always good to have.

Then select Tools -> Letters and mailings->Mail Merge and follow the steps (this is based on Office 2003) if these options aren't available, post what version of word and excel your working in and another member should be able to tell you how to do a mail merge in the version of office your workign in.

HTH
Andrew;?

edit ---

looks like you beat me to the punch Ken :)

edit ---

[Ken Puls]

looks like you beat me to the punch Ken :)

I don't get to do that often, especially when it involves Word! ;)

[Killian]

Hi Kurt,

I've just got time for a couple of comments at the moment (but I'll check back later)

I think the simplest way to do this would involve a little preparation (but not much code)

1. Save your contact data as a comma delimited text file (CSV)
2. Import the csv into into a new Outlook contacts folder (File|Import and Export & follow the wizard)
3. With your word doc, insert a bookmark where you want the title & name to go and save the doc as word template.
4. Now some code in Outlook to iterate through the contacts in the folder, create a new doc from the template in word, put their title, name, last name in the bookmark, save it, create a new mail, attach the doc, send the mail (using the contact info)

You could also take the opportunity to customise the email using the subject and body properties.
Also, you'll probably want to delete the Word file when your done.

The advantage of this appraoch is that Outlook is quite a good way to manage business contacts.
Hope this helps. If you decide to take this route, let me know and I'll help get some code together.

[stapuff]

sandam & kpuls -

Thanks for your thoughts. I have no experiance with Outlook so I was going to prebuild what I could before starting to mess with it. I think they have Excel & Word 2000.

If they had Lotus Notes which I use - this project would be done already. I would just force the message in the body of the e-mail via code and skip Word altogether.



I will take a look at mail merge. Sounds like the starting point.



Thanks,



Kurt

Killian -

Thank you for the post. I like your idea for what I see as simple, which is what I will need to provide for the in-laws. They know how to turn a com on and off, internet surf, etc, but when I start saying things like Dim or Macro or Mail Merge I get the "Deer in headlights" look. :wot :wot

I have completed your step 1 through 3. How can I accomplish step 4.

Again - Thanks,

Kurt

[Killian]

Hi Kurt,
I don't have Outlook at home so I'll post some code when I get back to work.

[Killian]

OK, time for step 4

You'll need to add a reference in the VBEditor to the MicroSoft Word Object Library (Tools|References) so we can control Word fom Outlook.

The following code should do more or less what you want - you'll need to update stuff kike the file paths, contact folder and bookmark name - but I must point out that I'm a bit short on time so the code is untested.
I don't think I've forgotten anything...: pray2:

Have to dash - hope it works out


'some string constants we'll use
Const TEMPLATE_PATH As String = "C:\VBAX\MailShot\MailShot.dot"
Const TEMP_FILE_PATH As String = "C:\VBAX\MailShot\TempFile.doc"

Sub Mailshot()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim ns As NameSpace
Dim olFldr As MAPIFolder
Dim c_item As ContactItem
Dim NewMail As MailItem
Dim strTemp As String
Set wdApp = New Word.Application
'if you want to see Word, uncomment next line
wdApp.Visible = True
Set ns = Application.GetNamespace("MAPI")
'the next line sets a reference to the folder I created for the contacts.
'Mine was a sub-folder in my Personal Folders'contacts folder
Set olFldr = ns.Folders("Personal Folders").Folders("Contacts").Folders("TEMP")
For Each c_item In olFldr.Items 'iterate through the contacts
'set up the attachment
Set wdDoc = wdApp.Documents.Add(TEMPLATE_PATH)
With wdDoc
'add the title and name to the bookmark
.Bookmarks("bmkTitleAndName").Range.Text = c_item.Title & " " & c_item.LastName
.SaveAs TEMP_FILE_PATH ' save the temp word file
End With
'create the mail item
Set NewMail = Application.CreateItem(olMailItem)
With NewMail
.To = c_item.Email1Address
.Subject = "" 'add string value here for subject of the email
.Attachments.Add TEMP_FILE_PATH 'attach the word doc
.Body = "" 'add string value here for body text of the email
.Send 'send the email
End With
Next
'you might want some extra code here to delete the temp word file, or alternatively
'at the end of each iteration, you could append a string with the details and time
'of each email and at the end, put that string into the temp word file and print it
'as a report of the mailshot
'tidy up
Set NewMail = Nothing
Set olFldr = Nothing
Set ns = Nothing
Set wdDoc = Nothing
wdApp.Quit
Set wdApp = Nothing
End Sub

[stapuff]

Killian -

Thanks for the post back. I appreciate your help. I (like you) have limited time, so I will attempt to work with what you provided. I will post back.

Again - Thanks,

Kurt

[Bob Phillips]

Kurt,


If you have the word document on the machine, easier to do it all from Excel, no mailmerge, no references, just change the values at the head to suit



Sub SendMail()
Const WordDoc As String = "C:\WordDocs\Golf Info.doc"
Const mailSubject As String = "More from you favourite Golf store"
Const mailBody As String = "This is to keep you up to date"
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True
Set oMailItem = oOutlook.CreateItem(0)
Set oRecipient = _
oMailItem.Recipients.Add(Cells(i, "D").valie)
oRecipient.Type = 1
oMailItem.Subject = mailSubject
oMailItem.Body = "Dear " & Cells(i, "A").Value & " " & _
Cells(i, "B").Value & " " & _
Cells(i, "B").Value & vbCrLf & vbCrLf & mailBody
oMailItem.Attachments.Add (WordDoc)
oMailItem.Send
Next i
End Sub

[MOS MASTER]

I will take a look at mail merge. Sounds like the starting point.

Hi Kurt, :D

I agree on sandam & kpuls because you're doing just what Word does best, a simple mailmerge to email!

VBA can never beat the speed off this Buildin Word feature!
Also consider the following. VBA Automation of Outlook will cause versions off Office > 2000 SP3 to popup a warning message that an application is trying to send email. (There are workarrounds but they will all slow the process down)

You won't get this warning with Word's mailmerge to email!
But remember this process is easy to automate...problem however when automating this process Security of Outlook wil see this as a possible breach and will show the dialog again.

I would simply setup a mailmerge document and have them press the button to execute.

Enjoy! :whistle: