Doug's Attachment macro-handling empty cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aeacsharp - 20 Feb 2006 15:38 GMT

Doug Robbins' tutorial and macro for merging with email attachments is
terrific -- thanks!

But in my case, I would like the macro to handle cases in which rows in
the "directory" word file are empty, moving on to the next row/record
email address. This case comes about because my directory has a fixed
number of columns, but the number of attachments in any row may vary,
from a minimum of 1, up to 10. My data source is an access table,
itself the result of a make-table query activated by a command button
on a form.

However, I do find the macro to work flawlessly when all the attachment
columns contain data with a valid path, with no empty cells.

Has anyone a suggestion for accomplishing what I want to do?

thanks!

Reply to this Message

aeacsharp - 20 Feb 2006 15:43 GMT

I;m sorry, i mis-stated something here:
"But in my case, I would like the macro to handle cases in which
**rows** in the "directory" word file are empty, moving on to the next
row/record "

for "rows" substitute the word "cells". I need help in handling empty
cells in the "directory" word file, so that the macro VB does not
"break" when an attachment path resolves to "".

my apologies!

Reply to this Message

Doug Robbins - Word MVP - 20 Feb 2006 16:55 GMT

The following modification to the code will check to see if there is a colon
in the cell, as there would be if it contains the path and filename of an
attachment, and only add the attachment if there is:

For i = 2 To Maillist.Tables(1).Columns.Count
Set Datarange = Maillist.Tables(1).Cell(Counter, i).Range
Datarange.End = Datarange.End - 1
If InStr(Datarange, ":") > 0 Then
.Attachments.Add Trim(Datarange.Text), olByValue, 1
End If
Next i

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> I;m sorry, i mis-stated something here:
> "But in my case, I would like the macro to handle cases in which
[quoted text clipped - 6 lines]
>
> my apologies!

Reply to this Message

aeacsharp - 20 Feb 2006 17:15 GMT

Outstanding, thanks so much, Doug. Yes, it does help. Works great,
saved us all some time up here.

Reply to this Message