Solved

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

Posted on 2013-11-18
5
1,007 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

685 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