Attaching files to email 2

[JTBorton]

I am trying to create a macro that will automatically save a copy of and email the current workbook. I don't want to use the .SendTo meathod because I want the user to have the opportunity to type a message or attach multiple items. I can open a blank mail form, add in recipients and subjects (thanks to a little lot of help) but I cannot attach files to it. Can anyone show me what I am doing wrong?

Definitions:
wbBookToMail: an excel workbook variable
Recipients: A variable to hold the mail recipients
TempFileName, TempFilePath, FileExtStr: variables to hold the different parts of the filename
FileFormatNum: A variable that defines the format of the file

[COLOR=darkblue]With[/color] wbBookToMail
    .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
    [COLOR=green]'On Error Resume Next[/color]
    CreateObject("WScript.Shell").Run "mailto:" & Recipients & "?subject=Test&attachment=" & TempFilePath & TempFileName & FileExtStr
    [COLOR=darkblue]On Error GoTo[/color] 0
[COLOR=darkblue]End With[/color]

-JTBorton
Another Day, Another Disaster

 

[TopJack]

Assuming you save your file before, you could use code similar to this for creating an e-mail without sending (assuming your e-mail client is MS Outlook aswell) ......

Sub create_email_for_editing()
[green]'This routine will require "Tools\References" to "Microsoft Outlook X.X Object Library"[/green]
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem

Set olApp = CreateObject("Outlook.Application") [green]'reference to Outlook[/green]
Set olMail = olApp.CreateItem(olMailItem)       [green]'reference to new e-mail[/green]

With olMail
    .To = "name@domain.com"     [green]'address oto send e-mail to[/green]
    .Subject = "email subject"  [green]'e-mail subject title[/green]
    .Body = "email message"     [green]'e-mail body text[/green]
    .Attachments.Add "c:\temp\my_file.xls", olByValue, 1  [green]'add attachment to e-mail[/green]
    .Display    [green]'display the e-mail for user to edit further[/green]
[green]'   .Send       'will send e-mail without editing[/green]
End With

End Sub

 

[JTBorton]

TopJack,
The code works great, but there are two issues:

Your solutions works, IF the user already have outlook open and running. If outlook is not open and running then I get the following error:

[!]Run-time error '-2113732605 (82030003)'
The operation failed.[/!]

Also, this method requires including reference to the outlook object library, which I had hoped to avoid, but am willing to deal with if that's the way it needs to be. I had hoped to stick with the CreateObject("WScript.Shell").Run ... because it does not require the outlook library. Like I said though, I'm flexible and looking to learn.

Any suggestions?
Thanks again for the help

-JTBorton
Another Day, Another Disaster

 

[TopJack]

The code should work with or without Outlook being open. The routine creates a reference to Outlook but does not need it to be open. It sounds like your receiving the error message because of some other issue - I tried to investigate what the error message was actually reporting but couldn't find out any more information, what is the exact error message you receive (word perfect).

Just in case it makes any difference the VBA reference I'm using is "Microsoft Outlook 11.0 Object Library".

 

[JTBorton]

I'm running Microsoft Outlook Library 10 and Excel library 10. The information posted above in red is the exact word-per-word error message. For convenience I will write it again:

[!]Run-time error '-2113732605 (82030003)'
The operation failed.[/!]

When I click on Help, the following information is provided from the Microsoft Visual Basic Help:
Automation error (Error 440)

When you access Automation objects, specific types of errors can occur. This error has the following cause and solution:
[ul]
[li]An error occurred while executing a method or getting or setting a property of an object variable. The error was reported by the application that created the object.

Check the properties of the Err object to determine the source and nature of the error. Also try using the On Error Resume Next statement immediately before the accessing statement, and then check for errors immediately following the accessing statement. [/li]
[/ul]

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

-JTBorton
Another Day, Another Disaster

 

[TopJack]

Thanks for the clarity JTBorton.

Looking through the net I found these two websites that might give us a clue whats happening:-

http://www.devnewsgroups.net/group/microsoft.public.office.developer.outlook.vba/topic57971.aspx

Try specifying the local host. Set objOutlook = CreateObject("Outlook.Application", "LocalHost"). Maybe also GetObject would help if Outlook is not open.

http://www.devnewsgroups.net/group/microsoft.public.office.developer.outlook.vba/topic58421.aspx

Maybe your install of Outlook is not registered properly. Try running Detect and Repair in Outlook. A local re-install of Outlook can also resolve the issue apparently.

Just some ideas. Its difficult for me to investigate when it runs fine for me under version 11.0 of the library.

Let us know how you get on.

 

[docjohn52]

subject=Test&attachment=" & TempFilePath

This looks like you're jamming everything into subject

subject=Test, attachment=" & TempFilePath

maybe semicolon? maybe just a space...

John

 

[JTBorton]

docjohn52,

jamming everything together is actually how you set the different fields when you use the
CreateObject("WScript.Shell").Run "mailto:... meathod. Any space will stop it dead in its tracks. For example, if I had ...mailto:<address>?subject=Whats Up&body=TEST TEST TEST" it would produce the following results:

To: <address>
Subject: Whats

replacing the space with %20 will give the space needed.
...mailto:<address>?subject=Whats%20Up&body=TEST%20TEST%20TEST" will produce

To: <address>
Subject: Whats Up
Body: TEST TEST TEST

Using a semicolon or comma as suggested produces this in the subject line:

To: <address>
Subject: Test,attachment=C:\DOCUME~1\JTBORT~1.002\LOCALS~1\Temp\Part

TopJack

I will look into the liscensing and whether or not outlook is installed properly. Give me a day or two to try it out on several different computers and I will get back to you. Thanks for the insight.

-JTBorton
Another Day, Another Disaster

 

[PHV]

Anyway, the mailto: URL syntax doesn't admit attachment ...

 

[ettienne]

Try using CDO for creating the emails. Just Google CDO and you should get examples.

 

[PHV]

thread705-1369407

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JTBorton]

Okay now I've hit a small stumbling block with CDO. How do you get it to show the message so that the user can edit it before it is sent? I cannot find the method for this, even on MSDN.

Now this is rather convenient:

With CreateObject("WScript.Shell").Run "mailto:... I cannot automatically attach files if I need to.

Using outlook causes the program to crash unless outlook is already open.

CDO does not allow you to make the email visible to the user for editing.

-JTBorton
Another Day, Another Disaster

 

[JTBorton]

[tab]TopJack

I tested the outlook protion that we discussed on different computers. It works for most, for some it doesn't. It doesn't work on any computers here at work. Then I tried adding the following to my code (see highlighted below) and it seems to be working fine now on every computer. I'm not sure why. I got it out of a VBA book that I have. I would still like to find a way that I can automatically attach files to an email message AND make it visible for the user to edit without Outlook, because not all of my users will have outlook in their office package.

[COLOR=darkblue]Dim[/color] olApp [COLOR=darkblue]As[/color] Outlook.Application
[COLOR=darkblue]Dim[/color] olMail [COLOR=darkblue]As[/color] Outlook.MailItem
[highlight]Dim oFolder As Outlook.MAPIFolder
Dim MoOutlook As Outlook.NameSpace[/highlight]

[COLOR=darkblue]Set[/color] olApp = CreateObject("Outlook.Application") [COLOR=green]'reference to Outlook[/color]
[highlight]Set MoOutlook = olApp.GetNamespace("MAPI")
Set oFolder = MoOutlook.GetDefaultFolder(olFolderOutbox)[/highlight]
[COLOR=darkblue]Set[/color] olMail = olApp.CreateItem(olMailItem)       'reference to new e-mail
    
[COLOR=darkblue]With[/color] olMail
    .To = "joshua.borton@lyondellbasell.com"
    .Subject = "email subject"  
    .Body = "email message"     
    .Attachments.Add TempFilePath & TempFileName & FileExtStr, olByValue, 1  
    .Display    
    [COLOR=green]'   .Send       'will send e-mail without editing[/color]
[COLOR=darkblue]End With[/color]

-JTBorton
Another Day, Another Disaster