Is it possible to attach a file to an outlook email from an excel macro? I know you can attach the active worksheet but can you attach a totally non related file??
I know this doesn't work, just hoping this will help you understand what I am talking about.
Example:
Please Login or Register to view this content.
Last edited by aaronp; 12-03-2012 at 05:07 PM.
Hi,
Try
.Attachments.Add ("C:\Users\Desktop\ThisFile.doc")
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Try something like:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Cant get that to work, unfortunately. Get an error at the attachments line.
What error? Whose desktop is it?
Regards, TMS
Run-time error '440'
The operation failed.
Ill give you my whole code so you can see everything im doing.
I did try changing the attachment to an existing file and still got the same results.Please Login or Register to view this content.
Last edited by aaronp; 12-02-2012 at 09:43 PM.
I suspect the 440 error is on the wrdDoc .SaveAs ... suggest you remove the brackets.
Regards, TMS
I'll try that when I can get back to my computer but I don't think that's it. The word document gets created and saved as it should.
Ok, which line does the code crash on? Have you, or can you, step through the code to the crash site?
Regards, TMS
It crashes at the attachments line
Step through it when it crashes and in the Immediate pane enter
Print "C:\Users\Joe\Desktop\" & wrdDate & " " & wrdName & ".doc"
What result do you get?
Are there any non permitted characters for file names e.g. |, * , :, ? etc.?
What are the values in Sheets("Samples").Range("AI6") and Sheets("Samples").Range("CC11")
I wondered about that too ... but I would have thought that the save would fail too.
Regards, TMS
Hi TMS,
Yes good point. I hadn't considered that and you're probably right.
Sounds like this is an instance when the actual workbook would be useful.
Ive tried attaching a file name "a.doc" and got the same error so I know this isnt the issue. also, if there were non permitted characters, wouldnt that stop the word document from being saved as well?Originally Posted by Richard Buttrey
Something i did try and got a different error was changing .attachment.add = "xxx.xxx" to .attachment = "xxx.xxx" this changed there error to saying it couldnt attach because the file was read only, which it wasnt but it seemed like a step in the right direction. i can upload my spreadsheet and word template if you guys would like?
also, while i was at home, when the word document was created, the table pasted into word didnt have borders (which is what i want). Now that I am at work continuing to work on it, the table pasted into word has borders now....what gives? I didnt change anything on that end.
You're going to have to upload the workbook. Almost impossible to say without seeing the problem in context.
Heres the speadsheet and the word template. The macro im working on is on the "Samples" sheet and is the button on the top. Eventually they will all be changed to that one, but the top one is the only one im working on now. And of course, your filename paths will be changed to where you save them at.
Thanks for your help guys!! I really do appreciate it!!
I think we may all curse at this one ...
Please Login or Register to view this content.
That is, no equals sign. In fact, I think Richard's original proposal works although you don't need the brackets which is where I was coming from. So, please accept my apologies!
Regards, TMS
Well done TMS,
Indeed so. Been tearing what remains of my hair out this afternoon and just not spotted that. Even got as far as testing Ron de Bruins send CDO email method.
lol...and it was just that simple. Thank you so much guys! I actually lost sleep over this last night...
Now, if i could pick your brains just a bit more so i can put the final touches on this workbook...is there a way to paste and center to the word document? I can live without it but it would look much nicer if this is an easy fix.
You're welcome.
It's just a little bit frustrating because I took my own working code and copied across equals sign and file name, taking out the brackets. Then, every time you look at it, you just see what you think should work.
Ho hum, lesson learned.
With regard to the centreing, I don't know the Word Object Model well enough to offer a solution here. Best suggestion is to record a macro in Word and look at what you needed to do and tweak that.
Regards, TMS
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks