Link to Outlook with file embedded in e-mail

jimamj

Board Regular
Joined
Feb 1, 2006
Messages
64
I want to create a hyperlink that generates a new e-mail with a word form embedded in the e-mail. Is there a way to do this? Any help would be great. Thanks.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Not easily, and not from a hyperlink. Excel does not provide direct support for quite that level of E-Mail interaction.

It is possible, however, to write a piece of VBA code that would invoke Outlook, create a new messgae, and set the recipient, subject, body_text and any attachments.

This is a sample of Early Binding code to do this... it assumes that Outlook is installed... it will have fatal errors if th euser does not have outlook installed. As an Early Binding structure, it requires that you have included Microsoft Outlook xx.x Object Library using Tools->References.

Code:
Sub attach_to_mail()
    
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim RecipName As String
    
    Set OutApp = CreateObject("Outlook.Application")
    
    Set OutMail = OutApp.CreateItem(olMailItem)
    
    
    With OutMail
        .To = "John_Doe@yahoo.com"
        .CC = ""
        .BCC = ""
        .Subject = "File Attached"
        .Body = "This E-mail contains attachments"
        .Attachments.Add "C:\Temp\word_file.doc"
        
        .Send
        '.display
        
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

Also be advised that using this method will display a dialog saying that an application is trying to access Outlook, it may be a virus, do you want to continue? Alternatvely, rather than uisng the .send method, you could use the .display method... which would show the full outlook window for a new message.
 
Upvote 0
Would it be possible to add a word file to the e-mail inside the e-mail rather than attached? The word form would actually appear in the body of the e-mail.
 
Upvote 0
Yup. But don't forget to add a reference to the latest version of the Microsoft Word Object Library.

I also remembered while I was playing with this code that Outlook, unlike other Office Applications, only allows a single instance to run at any given time. So I added code to test if there was already an instance running, and either use that instance, or create a new one if needed.

Good luck... I am not sure what level of functionality the Word Form will retain when brought into Outlook: Let me know how you make out.

Rich (BB code):
Sub Embed_Doc_In_new_mail()
    'for support contact paul.sasur@hs.utc.com
    'tools->references->Microsoft Outlook 11.0 Object Library
    'tools->references->Microsoft Word 11.0 Object Library
    
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim RecipName As String
    Dim WordApp As Word.Application
    Dim OLbCreated As Boolean
    
    'ignore any errors
    On Error Resume Next
    'attempt to capture an existing instance of Outlook
    Set OutApp = GetObject(, "Outlook.Application")
    're-set to stop on errors
    On Error GoTo 0
    
    'test to see if we successfully captured an existing instance of Outlook
    If OutApp Is Nothing Then
    
        'if no instance of outlook was found, create one
        Set OutApp = CreateObject("Outlook.Application")
        'remember that we created a new instance of outlook
        OLbCreated = True
        
    End If
    
    'create a new mail item (message)
    Set OutMail = OutApp.CreateItem(olMailItem)
    
    'create a new instance of Word... doesn't matter if there is already
    'an instance running, unlike Outlook, Word allows multiple instances
    Set WordApp = CreateObject("word.application")
    
    'set properties of the New Mail Item (Message)
    With OutMail
        .To = "John_Doe@yahoo.com"
        .CC = ""
        .BCC = ""
        .Subject = "File Embedded"
        
        'Body could be text, here we set the Content of an Existing Hardcodesd File
        .Body = WordApp.Documents.Open("C:\Temp\word.doc").Content
        'also can select a file from a dialog
'        .Body = WordApp.Documents.Open(Application.GetOpenFilename(Title:="Please choose a file to import", FileFilter:="Word Files (*.doc),*.doc")).Content
        
        'Can add files as attachments
'        .Attachments.Add "C:\Temp\word.doc"
        
        'use EITHER Send or Display Method, not both... send attempts to send in the
        'background, while Display will show the message, requiring the user to
        'click send
'        .Send
        .display
        
    End With
    
    'quit Outlook if we created a new instance, and reset object and item to nothing
    If OLbCreated Then OutApp.Quit
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    'quit the new instance of Word we created, and set object to nothing
    WordApp.Quit False
    Set WordApp = Nothing

End Sub
 
Upvote 0
Thank you so much for the help. I actually am probably going to put the button to trigger the e-mail on the word form itself. So the document will already be open. I am trying to make change the code, but I'm not sure how to call the document if it is already open. Essentially the user will fill out the form and click send e-mail. It will then imbed the document and generate the receiver and subject lines. I know this is different than what I originally stated, but the project has evolved. Thanks again for the help.
 
Upvote 0
Essentially the user will fill out the form and click send e-mail. It will then imbed the document and generate the receiver and subject lines. I know this is different than what I originally stated, but the project has evolved.

You can say that again... the code that I assumes it is being triggered from within Excel. I assume that the code to run this from word woul dbe similar (minus the creation of a new word application) but I haven't programmed in Word since version 9.0, so I really have no idea what kind of problems you will encounter.

Good luck!
 
Upvote 0
Let me ask you this quick question. What would be the code to activate a open mail message window that is ready to send the e-mail. I used your code and was trying.

Windows("Subject line of e-mail").Activate

I am getting a runtime error 5941 The requested member of the collection does not exist.

How can I activate that window? Thanks again.
 
Upvote 0
The windows collection of Word or Excel only recognizes windows that are part of that application... which is why you are getting an error message. You have one of two ways of capturing a window outside of the application: 1)In general terms, you can use WINAPI function calls to identufy windows through the Operating System, and manipulate them, or 2) Retrieve the appropriate collection from Outlook and parse it until you find an item that meets your criteria. Neither method is very easy, and I woul dhave to spend an awful lot of time figuring out how to do them.

Your question may seem like the more intuitive way of doing this, from an outsider's perspective, but from a programmer's perspective, it's a nightmare. It is ALWAYS easier to create a new instance or a new object than it is to locate and identify one that exists. When you create an object, you are able to immediately capture all of th eifo you need to manipulate it... trying to find an object that exists can be a huge pain.
 
Upvote 0
That does seem pretty complicated. Essentially, all I have to do is add the content from the word form to the e-mail. The code you did takes care of everything else. It works fine except for adding the form content. Thanks for getting me this far.
 
Upvote 0
MMMM, I'm just about done fo rthe day... let me think about this, and get back to you tomorrow, assuming a bigger hitter hasn't come along and fixed it for you. I think that what we may be able to do is use my code to open the Form from the Excel Code, allow the user to edit it, then trigger the rest of the code to finish the message insertion process...
 
Upvote 0

Forum statistics

Threads
1,214,614
Messages
6,120,517
Members
448,968
Latest member
Ajax40

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top