Solved

Compare Methods: FollowHyperlink  vs CreateObject(OutlookApplication) to generate an email from Excel using VBA

Posted on 2013-11-18
5
944 Views
Last Modified: 2013-11-18
Can someone please enlighten me?

How do the methods "CreateObject" and Followhyperlink compare, when generating emails from Excel, using VBA?

The following examples seem to do the same thing.

1: Using CreateObject Method

Sub newemail()
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.createitem(0)

    On Error Resume Next
    With OutMail
        .To = "person@email.com"
        .Subject = "File Review"
        .body = "Please review file located at: " & _
                vbCrLf & "<file://" & ActiveWorkbook.FullName & ">"
        .display
    End With

    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Open in new window


2: Using FollowHyperlink Method

Sub CreateEmail()
     
    Dim rcp, hlink, msg, subj
    
    rcp = "person@email.com"
    subj = "Please review this file"
    msg = "Please review file located at: " & _
    "%0A%0A" & "<file://" & ActiveWorkbook.FullName & ">"
    
    hlink = "mailto:" & rcp & "?"
    hlink = hlink & "subject=" & subj & "&"
    hlink = hlink & "body=" & msg
    
    ActiveWorkbook.FollowHyperlink (hlink)
End Sub

Open in new window



Is one method more robust than the other?
Is one method more versatile than the other?
What other Methods would it be useful to know about?
HAve I asked the right questions?

Many thanks
David
0
Comment
Question by:David Phelops
  • 3
  • 2
5 Comments
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
Comment Utility
Hi David,

The fundamental difference between the two approaches is that you are explicitly using Microsoft Outlook when creating an object, but following a hyperlink ("mailto:" protocol) will create an e-mail within the default e-mail application on the run-time machine.  This may not be MS-Outlook, of course.

In fact, MS-Outlook may not be present on the run-time machine.  If this was the case in the first approach, then the code would fail.  However, the second approach would not (assuming at least one e-mail application was installed & associated with the "mailto:" protocol).

Overlooking this restriction, the CreateObject approach is more flexible as you can specify far more parameters (properties) compared to the hyperlink (as only "subject", "to", "cc", "bcc", & "body" parameters are supported in the query string).  There is also a maximum number of characters able to be passed via a URL/query string parameters, & the creation of HTML-enabled e-mail body content needs to be 'escaped' (converted to ASCII representations of Unicode characters; "RFC5137").  This is not the case using the Document Object Model syntax of MS-Outllook.

When executing the CreateObject approach you will have more control over the reporting of runtime errors & failures to create/send an e-mail.  The "mailto: " approach either works, or it doesn't... & you may never know if it has worked successfully as once the command has been passed to the e-mail application you have no idea if the e-mail was created &/or sent.

BFN,

fp.

PS. Ron de Bruin's "Excel Automation" site contains some variations on sending e-mail content:

[ http://www.rondebruin.nl/win/section1.htm ].

You may also wish to look at the use of "Collaboration Data Objects" [CDO] (previously called "OLE Messaging" or "Active Messaging"):

[ http://www.rondebruin.nl/win/s1/cdo.htm ].
0
 

Author Comment

by:David Phelops
Comment Utility
Thank you fp for this comprehensive (and fast!) response.
I'm slowly digesting the information.

I had a look at the RFC5137 - OW, that is one document too far for me!!

I have a had a peek at Ron de Bruin's site - thanks for that signposting.

Cheers
David
0
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
You're very welcome.

Sorry, yes, the "RFC5137" document is perhaps too much information... just here for those looking for keywords in the site's search engine in the future.

Happy reading! :)
0
 

Author Closing Comment

by:David Phelops
Comment Utility
The post answered my questions precisely, concisely and was intelligent and friendly in tone. The signpost to further reading helped as well.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
:)

Thanks for accepting the answer & for not mentioning my spelling of "MS-Outllook" (with double 'l').
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now