Reply
 
Thread Tools Display Modes
  #1   Report Post  
Back2Basics Back2Basics is offline
Junior Member
 
Posts: 2
Default Mail merge Attachment problems

I have been trying to send a series of email using the mail merge so that associated attachments are sent with each email - different for each email address.

I am aware that there is a piece of code that is meant to sort this out for me, I have tried using it and it doesn't seem to be working as it should. I set up a test spreadsheet with 10 cells with my email address in and 10 cells with different files paths in the next column. I followed all of the directions for the VBA Code and eventually got to a stage where i was prompted for the subject for the email and then it continued to - what I thought was send the emails.

I was sat there for a few minutes clicking yes to all of the messages, I know there is a program for this but I can not DL at work. After clicking well over 30 times I tried to stop, ended up ending the task through the task list. Why do i have to click over 30 times when I am only sending 10 emails? And why after clicking over 30 times did I not recieve a single email?

Can anyone help me on this issue?

TIA
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default Mail merge Attachment problems

I assume that you must be referring to Doug's macro
http://word.mvps.org/FAQs/MailMerge/...ttachments.htm ?
I thought there was something amiss when you mentioned that the macro
prompted for a subject, whereas my older copy of Doug's macro doesn't do
that. The older copy - listed below - does work correctly. I briefly tested
the version currently on the web site and that doesn't work for me. It
either merges just one record or none. No doubt Doug will be along later (if
he is back from holiday) to put it right. If not I will take a look this
afternoon when I have a bit more time.

Interestingly, I no longer get the prompt to confirm every record from
Office 2007. I am not currently running ClickYes. I haven't tested with
Office 2003 yet. That too will have to wait until later.

If merging with attachments from Word is going to be a feature of your
employment, you could suggest to your IT department that they install
MAPILab's Mailmerge toolkit add-in for Outlook
http://www.mapilab.com/outlook/mail_merge/ which adds a few more bells and
whistles to the process. But the basic core of Doug's macro (below) works as
stated - it's just the current iteration on the web page that seems to have
wobbled.


Sub EmailMergeWithAttachments()
Dim Source As Document, Maillist As Document
Dim Datarange As Range
Dim Counter As Integer, i As Integer
Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
Set Source = ActiveDocument
On Error Resume Next
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If
With Dialogs(wdDialogFileOpen)
.Show
End With
Set Maillist = ActiveDocument
Counter = 1
While Counter = Maillist.Tables(1).Rows.Count
Source.Sections.First.Range.Cut
Documents.Add
Selection.Paste
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Body = ActiveDocument.Content
Set Datarange = Maillist.Tables(1).Cell(Counter, 1).Range
Datarange.End = Datarange.End - 1
.To = Datarange
For i = 2 To Maillist.Tables(1).Columns.Count
Set Datarange = Maillist.Tables(1).Cell(Counter, i).Range
Datarange.End = Datarange.End - 1
.Attachments.Add Trim(Datarange.Text), olByValue, 1
Next i
.Send
End With
Set oItem = Nothing
ActiveDocument.Close wdDoNotSaveChanges
Counter = Counter + 1
Wend
If bStarted Then
oOutlookApp.Quit
End If
Set oOutlookApp = Nothing
End Sub


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Back2Basics wrote:
I have been trying to send a series of email using the mail merge so
that associated attachments are sent with each email - different for
each email address.

I am aware that there is a piece of code that is meant to sort this
out for me, I have tried using it and it doesn't seem to be working
as it should. I set up a test spreadsheet with 10 cells with my email
address in and 10 cells with different files paths in the next
column. I followed all of the directions for the VBA Code and
eventually got to a stage where i was prompted for the subject for
the email and then it continued to - what I thought was send the
emails.

I was sat there for a few minutes clicking yes to all of the messages,
I know there is a program for this but I can not DL at work. After
clicking well over 30 times I tried to stop, ended up ending the task
through the task list. Why do i have to click over 30 times when I am
only sending 10 emails? And why after clicking over 30 times did I not
recieve a single email?

Can anyone help me on this issue?

TIA



  #3   Report Post  
Back2Basics Back2Basics is offline
Junior Member
 
Posts: 2
Default

Thanks Graham,

However when I tried using the code you pasted in it doesn't work. There seem to be quite a few little erros that stop it working. The first one was something about the line:

If Err 0 Then -- It doesn't seem to like this

and another error occurs for the line:

End if -- It says there is no bolck if


I'm not very good with VBA so don't really know how to fix it if i can - but for some reason it doesn't work.
  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default Mail merge Attachment problems

The following is a version of Doug's earlier macro which works for me and
adds the Subject selection. I'll mention the other problem to him for when
he gets back.

Sub EmailMergeWithAttachments()
Dim Source As Document, Maillist As Document
Dim Datarange As Range
Dim Counter As Integer, i As Integer
Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
Dim sSubject As String
Set Source = ActiveDocument
On Error Resume Next
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If
With Dialogs(wdDialogFileOpen)
.Show
End With
sSubject = InputBox("Enter subject for all the messages", "E-Mail Subject")
Set Maillist = ActiveDocument
Counter = 1
While Counter = Maillist.Tables(1).Rows.Count
Source.Sections.First.Range.Cut
Documents.Add
Selection.Paste
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Body = ActiveDocument.Content
.Subject = sSubject
Set Datarange = Maillist.Tables(1).Cell(Counter, 1).Range
Datarange.End = Datarange.End - 1
.To = Datarange
For i = 2 To Maillist.Tables(1).Columns.Count
Set Datarange = Maillist.Tables(1).Cell(Counter, i).Range
Datarange.End = Datarange.End - 1
.Attachments.Add Trim(Datarange.Text), olByValue, 1
Next i
.Send
End With
Set oItem = Nothing
ActiveDocument.Close wdDoNotSaveChanges
Counter = Counter + 1
Wend
If bStarted Then
oOutlookApp.Quit
End If
Set oOutlookApp = Nothing
End Sub


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Graham Mayor wrote:
I assume that you must be referring to Doug's macro
http://word.mvps.org/FAQs/MailMerge/...ttachments.htm ?
I thought there was something amiss when you mentioned that the macro
prompted for a subject, whereas my older copy of Doug's macro doesn't
do that. The older copy - listed below - does work correctly. I
briefly tested the version currently on the web site and that doesn't
work for me. It either merges just one record or none. No doubt Doug
will be along later (if he is back from holiday) to put it right. If
not I will take a look this afternoon when I have a bit more time.

Interestingly, I no longer get the prompt to confirm every record from
Office 2007. I am not currently running ClickYes. I haven't tested
with Office 2003 yet. That too will have to wait until later.

If merging with attachments from Word is going to be a feature of your
employment, you could suggest to your IT department that they install
MAPILab's Mailmerge toolkit add-in for Outlook
http://www.mapilab.com/outlook/mail_merge/ which adds a few more
bells and whistles to the process. But the basic core of Doug's macro
(below) works as stated - it's just the current iteration on the web
page that seems to have wobbled.


Sub EmailMergeWithAttachments()
Dim Source As Document, Maillist As Document
Dim Datarange As Range
Dim Counter As Integer, i As Integer
Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
Set Source = ActiveDocument
On Error Resume Next
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If
With Dialogs(wdDialogFileOpen)
.Show
End With
Set Maillist = ActiveDocument
Counter = 1
While Counter = Maillist.Tables(1).Rows.Count
Source.Sections.First.Range.Cut
Documents.Add
Selection.Paste
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Body = ActiveDocument.Content
Set Datarange = Maillist.Tables(1).Cell(Counter, 1).Range
Datarange.End = Datarange.End - 1
.To = Datarange
For i = 2 To Maillist.Tables(1).Columns.Count
Set Datarange = Maillist.Tables(1).Cell(Counter, i).Range
Datarange.End = Datarange.End - 1
.Attachments.Add Trim(Datarange.Text), olByValue, 1
Next i
.Send
End With
Set oItem = Nothing
ActiveDocument.Close wdDoNotSaveChanges
Counter = Counter + 1
Wend
If bStarted Then
oOutlookApp.Quit
End If
Set oOutlookApp = Nothing
End Sub



Back2Basics wrote:
I have been trying to send a series of email using the mail merge so
that associated attachments are sent with each email - different for
each email address.

I am aware that there is a piece of code that is meant to sort this
out for me, I have tried using it and it doesn't seem to be working
as it should. I set up a test spreadsheet with 10 cells with my email
address in and 10 cells with different files paths in the next
column. I followed all of the directions for the VBA Code and
eventually got to a stage where i was prompted for the subject for
the email and then it continued to - what I thought was send the
emails.

I was sat there for a few minutes clicking yes to all of the
messages, I know there is a program for this but I can not DL at
work. After clicking well over 30 times I tried to stop, ended up
ending the task through the task list. Why do i have to click over
30 times when I am only sending 10 emails? And why after clicking
over 30 times did I not recieve a single email?

Can anyone help me on this issue?

TIA



  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default Mail merge Attachment problems

Sometimes when code is pasted from messages such as this, there are line
ending issues as a result of the message creation process. Try deleting the
errant line and retype it into the editor - see also
http://www.gmayor.com/installing_macro.htm

Delete all that you pasted into the vba editor from both the web page and my
message and replace it with the code from my later message and see how you
get on with that. If you can't get it to work, e-mail me via the link on the
home page of my web site and I will send it to you as an add-in.

Which Word version are you using? This has been tested with 2003 and 2007

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Back2Basics wrote:
Thanks Graham,

However when I tried using the code you pasted in it doesn't work.
There seem to be quite a few little erros that stop it working. The
first one was something about the line:

If Err 0 Then -- It doesn't seem to like this

and another error occurs for the line:

End if -- It says there is no bolck if


I'm not very good with VBA so don't really know how to fix it if i can
- but for some reason it doesn't work.



Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problems with Mail merge with attachment Emily Mailmerge 1 December 31st 08 12:01 AM
Email Mail Merge w/ Attachment Problems Melissa Mailmerge 8 November 2nd 08 07:07 AM
Mail Merge mass mailing with e-mail sentence and attachment BL Mailmerge 1 August 23rd 08 06:29 AM
Word Mail Merge to E-mail message with attachment? Frank D. Nicodem, Jr. Mailmerge 4 April 25th 07 07:01 PM
mail merge email attachment problems Jimmy Mailmerge 1 April 21st 05 08:30 AM


All times are GMT +1. The time now is 04:51 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"