Solved

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

Posted on 2013-11-18
5
963 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
ID: 39655834
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
ID: 39656062
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 ]
ID: 39656067
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
ID: 39656083
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 ]
ID: 39656150
:)

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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
Read this checklist to learn more about the 15 things you should never include in an email signature.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

864 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

24 Experts available now in Live!

Get 1:1 Help Now