Error handling for macro that sends emails with different attachments

[kylegascho]

Hi all!
I'm new here, so I am so sorry if I do anything wrong on this post!

I have setup a module that will send a bunch of emails to numerous individuals. Each email has its own specific attachment that is sent.
All of these attachments are in a specific folder, and follow a naming convention based on who it needs to be sent to.

Column A: Business Code
Column B: Business Name
Column C: The email address of the person the email is being sent to
Columns D & E: The email addresses of the people being CC'd on the email.

The problem I am having is that sometimes there is an error made in the naming convention (by the person who uses the program).
The naming convention for each attachment is the value of Column A + _ + 10th + the file extension (usually an xls file).

This brings up a run-time error when it gets to a row where it can't find the attachment.

What I would like to do is have it operate so that it continues to send the rest of the emails, and then after that is completed have a MsgBox or something appear that states which row the error(s) happened at, so the user can send those emails individually with the attachments the module couldn't find.

Please Login or Register  to view this content.

I had to remove the at symbol from the line under "attachmentname" as it thought it was a link of some kind.

I am totally open to any and all suggestions! Thank you in advance for your help!

[Kenneth Hobson]

One could have a status column that is True for sent or False for not sent or Missing Attachment or other.

Another approach might be to Save without attachment. Later, they could open in Outlook's Draft folder and manually add it.

Another might be to prompt for a file if missing.

Or, a combination of methods above.

[kylegascho]

Thanks so much, Kenneth!
Would you have an example of code I could add to my module that would perform your first suggestion? I guess it would also need code to not send emails to the individuals that there are attachments missing for???

[Greg M]

Hi there,

I can't really test this without access to your workbook, but see if the highlighted additions to your code get you moving in the right direction:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[kylegascho]

Hey Greg!

It works great, thank you so, so much! I'm wondering if there is a way to make it so it doesn't include the first row? The first row has the headings for my columns.
Is there a way to have it start on the second row?

Thanks again!

Kyle

[Greg M]

Hi again,

Many thanks for your feedback.

A few questions:

I'm wondering if there is a way to make it so it doesn't include the first row?

Does "the first row" correspond to Row 1 of the worksheet, or is it the first row of a Range/Table contained in the worksheet?

Is there a way to have it start on the second row?

Does "it" refer only to the newly-added checking facility, or to the whole process? I.e. at present, does the application try to send an email using the values contained in your Header Row?

Regards,

Greg M

[kylegascho]

Sorry about that!

Does "the first row" correspond to Row 1 of the worksheet, or is it the first row of a Range/Table contained in the worksheet?

Yes! I do mean Row 1 of the worksheet.

Does "it" refer only to the newly-added checking facility, or to the whole process? I.e. at present, does the application try to send an email using the values contained in your Header Row?

The newly-added checking facility is what I am referring to. The application doesn't try to send an email using the values contained in my Header Row (Row 1).

My apologies! I should have been more specific!

Thanks so much for your help!

Kyle

[Greg M]

Hi again Kyle,

Many thanks for your prompt reply.

See if the following addition to the previous code does what you need:

Please Login or Register  to view this content.

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

[kylegascho]

Hey Greg,

That didn't seem to work.
I attempted to upload my excel file, but it didn't seem to work.
Any other suggestions or ideas on how to prevent Row 1 being included in the newly-added checking facility?

You have no idea how much I appreciate your help!
Thanks so much!
Kyle

[Greg M]

Hi again Kyle,

Ok on trying to upload your workbook without success.

I'm trying to visualise the layout of Column C of your worksheet - does it contain a Header Row (Row 1) followed by a value like "?*?*.?*" in every occupied row? Do some rows contain values that are NOT like "?*?*.?*"? Are you using the "If cell.Value Like "?*?*.?*" Then" statement just to skip over the Header Row? If this is the case then it would probably be simpler to start scanning from Row 2 downwards, and this would mean that the new checking facility would not report the Header Row as a "problem".

If you can let me know the situation I'll see what I can do.

Regards,

Greg M

[Kenneth Hobson]

I think you can attach a file now with 5 posts done. Some forums require 10 so I am not sure. Try it and see. Click Go Advanced button in lower right of a reply.
Click Manage Attachments hyperlink below reply box.

Click Choose File and then after selecting, click Upload button.

[kylegascho]

Hey Greg,

I got the excel file attached here.

So in Column C (This is the column that I have all of the email addresses in that are being sent 'To").
Other than "C1", every cell in Column C has an email.

Cells A1, B1, C1, D1, and E1 are all headers.

So, for example: Cell A2 contains part of the value for the file name that is being attached to the email.
Cell B2 contains the business name. Cell's C2, D2, and E2 contain the email addresses for the contacts at the business from Cell B2
And A2 contains the specific attachment for that business.


Thanks so much!

Kyle

[kylegascho]

Thanks so much, Kenneth!

[Greg M]

Hi again Kyle,

Many thanks for that - having a proper view of the worksheet makes things a lot easier.

It shouldn't be too difficult to provide what you need, but it'll be tomorrow before I can get around to doing it - busy day here today!

Watch this space!

Regards,

Greg

[Kenneth Hobson]

This is a way to do the Status helper column. If no attachment was found, it puts "Missing File" and skips sending. If sent, it puts "Sent: date time".

Change the column F for Status helper column to suit. Comment out .Send and uncomment .Display to test.

Please Login or Register  to view this content.

[Greg M]

Hi again Kyle,

See if the following code does what you need - it skips the first (header) row and records the Row Number and Business Name associated with any email whose attachment could not be located.

Please Login or Register  to view this content.

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

[kylegascho]

Thanks so much, Greg!

Yes, it was a busy day for me as well! Hopefully today isn't as bad for you!
That worked perfectly! I really appreciate your help!

I've been working on this for a co-worker, and you have just helped in saving her over 10 hours of work each month!
AMAZING!

Kind Regards,

Kyle

[Greg M]

Hi again Kyle,

Many thanks for your feedback.

Delighted to hear that I've saved your co-worker some time - that's what computers are for!

Glad I was able to help.

Regards,

Greg M