Forum Home
Press F1
Thread ID: 63993	2005-11-29 09:40:00	VBA Code to e-mail an attached file?	andrew93 (249)	Press F1

Post ID	Timestamp	Content	User
408577	2005-11-29 09:40:00	Hi everyone After trawling the web all afternoon and trying about 4 different pieces of VBA code, I am now pulling my hair out. I'm using MS Access and I need a VBA script to attach a pdf file (from outside of the database, but the file is resident on the PC) to a newly created e-mail. Has anyone here used VBA to attach a file to an e-mail ? I would prefer something that is not platform specific (i.e. not just for Outlook). A lot of the posted VBA solutions assume the mail client is Outlook but I use both Outlook and Thunderbird and one of my users will be using Outlook Express. Almost every solution I've tried either does not work (as in it doesn't respond or there is a bug and it won't run) or the generic solutions create the e-mail but they don't actually attach the file! (even if I use a sleep command to slow the process down) Has anyone got this to work using VBA? If so, would you be happy to share your VBA code? TIA, Andrew	andrew93 (249)
408578	2005-11-30 08:03:00	Hi Andrew hows things. :-) See the DoCmd.SendObject example in MSAccess VBA. I have both Outlook and Thunderbird installed with Thunderbird as my default mail client. When using this statement it used Thunderbird which suggests the statement will use the default. I have seen solutions for Lotus,MS Outlook etc but they were using statements associated with those applications but hopefully SendObject will do the business. regards, Graham	Parry (5696)
408579	2005-11-30 08:45:00	Gidday Graham Long time no see..... ;) Thanks for the response. Unfortunately the SendObject command in Access won't allow me to pick up a pdf document (it only allows things like rtf, txt etc.). The most likely looking code I have found is here (www.tek-tips.com) that is not platform specific - it goes through the motions and creates the e-mail but just won't attach the dang file - I think it is the "&Attach" line that is the problem. The reason I say that is I introduced a 10 second 'sleep' into the routine to ensure the pdf was first created. If I can't get a response here I will post the question on either VBAX or MrExcel. Cheers, Andrew	andrew93 (249)
408580	2005-11-30 09:03:00	Hi Andrew, a bit tired so havent tried this but it seems this is using an API call to the Windows shell32 . dll file . In reading it says you need several textboxes to represent the variables but of course you can hard code to test it . I suspect the txtAttachment textbox would represent the full path to the file so if you declare a variable and populate a string path to this (eg txtAttachment = "c:\myfile . pdf") then use static CC values etc as a test then see what happens . MSDN does have info on api's but Ive found it to be quite superficial unless you already have a good idea how to call the functions needed . Good luck :) EDIT: Ensure you save the VBA code before running . When API calls bomb they tend to rudely kill the application without a second thought .	Parry (5696)
408581	2005-12-03 08:42:00	Hi Graham I think I have made the correct API call (see below) and I have hard-coded a lot of values for the purposes of testing but I just can't get the file to attach itself to the draft e-mail. Can you see anything wrong with this? '**Module (space formatting added to make it easier to read): Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long '**Code attached to a button: Dim stext As String 'Hard coded parts of the e-mail, stripped down to the minimum stext = "mailto:me@myaddress.co.nz?" stext = stext & " & Subject=" & "Document attached" stext = stext & " & Body=" & "Please find the document attached" stext = stext & " & Attach=" & "C:\test.txt" 'Launch default e-mail Call ShellExecute(hwnd, "open", stext, vbNullString, vbNullString, SW_SHOWNORMAL) Can you see where I have am going wrong with this? Thanks, Andrew	andrew93 (249)
408582	2005-12-03 10:54:00	Hi Andrew, I can get an email to open up nicely with all the values in the correct places but the darn file wont attach. No error though so in looking to see what syntax the mailto statement takes I have found several comments from people that mailto doesnt accept an attachment as a parameter. See comment here... www.codecomments.com Going to bed now but if I find something that works I'll let you know.	Parry (5696)
408583	2005-12-03 20:40:00	Thanks for that Graham. I had a look at the link and I suspect you are right. No matter how much I change the code or reword the &Attach part, the file won't attach itself so I guess I can't use the "mailto:" command. I had a hunt around and found some code that does CDO and/or MAPI here. (www.devx.com) But I'm back to my original problem in my first post. I copied it exactly as they had it and the CDO code won't work (it requires a CDO for NTS reference and I'm not sure where to get that from and I'm not sure I have the right software* for it to work - I think it requires MS Exchange Server). If I bypass the CDO code and jump straight into the MAPI code, I get an error "424 object required" which I believe is happening in this part of the code : With frmEmailCommon.MAPIMessages .SessionID = frmEmailCommon.MAPISession.SessionID MsgBox "session ok" .Compose I think it is happening there because the MsgBox I inserted does not appear, yet earlier message boxes do. *Software : the relevant software I'm running is Win XP Pro, MS Office Pro 2003 with VBA6.3, Mozilla Thunderbird and occasionally MS Outlook (but I don't want to rely on using Outlook, given one of my users also uses Outlook Express). The error occurs when I am running either mail software. I appreciate your help and any pointers are appreciated. Andrew	andrew93 (249)
408584	2005-12-03 21:39:00	Who would have thought that a relatively simple thing would prove to be such a mission? I cant even get as far as you in my code LOL. I get an "Undefined User Type" error on line the following line in the SendMail sub... Dim objSendMail As CDONTS.NewMail Trouble is its VB code which may require some massaging to make it work for VBA. I did however find the following reference in Access... Microsoft CDO for Exchange 2000 Library EDIT: PS I have the same set up as you with WinXp pro, office 2003 & using thunderbird.	Parry (5696)
408585	2005-12-03 21:56:00	I got the same error and that's when I researched the CDO for NTS thing - but like I said I think it's for MS Exchange Server. The way I jumped over it was to change the button to directly call the 'SendEmailMAPI' sub routine (funny how it doesn't auto jump to MAPI on the error - I thought it was designed to do that). I agree that this should be a relatively simple task but it has been almost a week now and I am absolutely no closer to gettting this solved. I have tried about 10 different pieces of code and I'm 10 pages deep into Google searches and I still can't find anything - most scripts are written for Outlook. If only the DoCmd.SendObject would accept files external to the database then this wouldn't be a major problem. Also, my relying on other peoples code because I don't understand the process is a major hurdle - maybe it's time to go on a course to learn about API, CDO, MAPI etc. I might give one of the Outlook scripts a whirl and let you know how it goes. Andrew	andrew93 (249)
408586	2005-12-03 22:24:00	Ok, this is 'sort of' working but boy it leaves a bad taste in the mouth. First attempt using Outlook went swimmingly well (using code from here (support.microsoft.com)). After grappling with this for so long, it leads me to think this is a compatibilty issue with non-MS software. Whilst I'm happy it works, I guess Thunderbird is about to become redundant on this PC. I would prefer this to be non-platform specific but I guess I can't have it all..... A	andrew93 (249)
1