Send Workbook as Attachment

**amdk8800** · 05-05-2010, 06:55 AM

Hi Gurus,

I have a workbook that needs to be sent as attachment on the click of a button but the following section of the code that caters for this macro gives me error on some of the computers in the office, and completely fine on others. The error is "TempWB.FullName = <Object variable or With block variable not set>". Here is the section that should get you into context:

Please Login or Register  to view this content.

Can you help?
Thanks guys.

**DonkeyOte** · 05-05-2010, 07:10 AM

Per the rules please add CODE tags to your first post, eg:

[*CODE*]
your VBA
[/*CODE*]

without the *

Your Error Handler would permit getting to the point of adding the attachment even if TempWB was Nothing - if that were the case then the attachment line would fail.

We don't know from the code how you're determining strAttachmentName but if it's the same as any file open in that instance of XL then it follows you can't open the file - the Handler will simply skip through this and TempWB will be Nothing.

I would however make the point that given the temp file is seemingly a straight up copy of ThisWorkbook then as I see it you don't need to open the copied version to determine whether or not it should be sent - after all the same value resides in the same sheet and same cell in ThisWorkbook does it not ?

**amdk8800** · 05-13-2010, 04:55 AM

Hi DonkeyOte,

Thanks for your reply. The strAttachmentName is defined by a cell value and a ComboBox, which should not coincide with any other file because my routine deletes TempWB after the send procedure. But even if there is a file with exactly the same name at exactly the same place, the script would set TempWB to that file even if it wasn't the intended one. Or am I wrong?

On your point about not needing to open TempWB, how do I set the workbook variable with a variable name without opening it?

**teylyn** · 05-13-2010, 05:05 AM

amdk, as DonkeyOte already requested: Change your tags to proper code tags!! Just edit your first post and remove the * signs in the tags.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**DonkeyOte** · 05-13-2010, 05:05 AM

please see my note re: "without the *" regards CODE tags

My point re: the VBA was that the file being saved is a direct copy of the current file therefore you can validate based on the master file without ever needing to open the copy.

Please Login or Register  to view this content.

of course this assumes that the file has saved successfully in the first instance (you might want to validate that)

**amdk8800** · 05-14-2010, 01:03 AM

Dear DonkeyOte,

Your code is definitely more to the point and worked exactly as I need it to. I have tested it on some of the computers that were having trouble before and it seems the issue has been resolved. Thank you for taking your time investigating and help out!!

Amk8800.