Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2013-11-18
5
Medium Priority
?
1,172 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 2000 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Here is why.
This guide provides step by step instructions on how to convert an Offline(.ost) to a Personal file(.pst). A different situation occurs when a conversion of OST file to PST format is needed. Use the described manual methods and SysTools OST to PST C…
Watch the video to learn how one can deal with PST file corruption issue with an outstanding Kernel for Outlook PST Repair Tool easily. Using this tool, non-technical users can swiftly perform the repair process to restore their essential data witho…
To export Lotus Notes to Outlook PST or Exchange and Domino Server files to Exchange Server or PST files with ease, go for Kernel for Lotus Notes to Outlook conversion tool. Through the video, you can watch the conversion process. A common user with…

579 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