Creating an e-mail message from Word VBA (VBA Word 2000)

Topic

New Reply

Sorry to post so many messages in succession. You guys are great to help out so much. Let me know if I’m taking advantage.

I’d like to open a new e-mail message window from Word via VBA. I then need to populate the To and Subject boxes and send.

If it would make it easier, we can assume Outlook is the e-mail client. That will be true most of the time, but there may be exceptions.

Thanks again for the help!!
Troy

Reply | Quote

Replies

Troy, you have used up your requests for the month. And it’s only the 4th!

To automate Outlook from another Office application, you have a lot of power but a few issues.

First, here are some code samples:

Sample #1
Sample #2

Second, there are macro security issues that will arise on machines that have certain security settings. For example, users might get a dialog asking them to approve the access. This is discussed regularly on the Outlook board, and maybe here as well, so you could search for more info.

Third, if a computer has multiple profiles set up in Corporate Workgroup mode, and no default, you might have to logon:

Sample #3

Hope this helps.

Reply | Quote

Thanks for the code samples. I could probably get one of these to work.

HOWEVER, it seems that all of them require the reference to be set to msoutl9.olb. I’ve searched through a previous posting on how to set this programmatically. Though it is a little scary, I’m willing to give it a try. However, what I could not seem to find is how to find the GUID or whatever would be necessary to set this reference programmatically.

I can almost get things to work like I want to with:

ActiveDocument.FollowHyperlink Address:=”mailto:email.address@mailbox.com”, NewWindow:=True

I just don’t know how to add a subject and send this. I’m thinking it could be done, but I just don’t know how.

If you could help with either of these I’d appreciate it!!
Troy

Reply | Quote

Troy,

The earlier thread you’re likely referring to (“Adding a reference to an object library in code”) appears to contain the solution to this question – first set the reference manually, and then run the code in Andrew Cronnolly’s post 100288 to determine the GUID.

Gary

Reply | Quote

Thanks Gary! I think I just forgot how to use this info (not to mention that’s a long, but good, post to wade through to find the final outcome.) Now I got it.

Troy

Reply | Quote

You could just use CreateObject(“Outlook.Application”) as that should be universal across all versions of Outlook.

Arguably late binding isn’t as efficient for the end user. Compared with debugging the alternatives, it is much more efficient for the programmer.

Reply | Quote

Edited by TroyWells on 06-Aug-02 15:10.

Alright. Everything is working great IF Outlook is open. However, on PCs when Outlook is closed, I get the following error when connnecting to Outlook via VPN:

Run-time error ‘-1940897787 (8c504005)’:

Cannot create the e-mail message because a location to send and receive messages could not be found. To add a location, click the Tools menu, click Services, and then click the Delivery tab.

This is happening at the following line of code:

Set msgItem = olApp.CreateItem(CurrentItem)

EDIT START*****************************************************
On other PCs connected directly to the network, I get the following error:

Run-time error ‘-2079063791 (84140111):

The server is unavailable. Contact the system administrator if this condition persists.

This is happening at the following line of code:

olNameSpace.Logon ProfileName, PasswordIfAny, blnShowDialog, False

EDIT END********************************************************

The following is the code in the procedure (btw this is a compilation of parts and pieces from the code samples you gave):

Declarations

Private Declare Function GetOpenFileName Lib “comdlg32.dll” Alias “GetOpenFileNameA” ( _
pOpenfilename As OpenFileName) As Long

Private Declare Function GetActiveWindow Lib “user32” () As Long

Private Declare Function GetFileTitle Lib “comdlg32.dll” Alias “GetFileTitleA” ( _
ByVal lpszFile As String, _
ByVal lpszTitle As String, _
ByVal cbBuf As Integer) As Integer

Private Type OpenFileName
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Public CanceledSelection As Boolean

Public GlobalTemplatePath As String
Public FileNameAndPath As String
Public FileNameOnly As String
Public DOTversion As String

Procedure

Public Sub RegisterTemplate()
Dim olApp As Outlook.Application, olNameSpace As Outlook.NameSpace
Dim msgItem As Outlook.MailItem, msgRecip As Recipient

‘Ask the user if they want to register.
If MsgBox(“Do you want to register the installation of this template?” & vbCrLf & vbCrLf & _
“If you register, it allows us to contact you when more features or bug fixes are available for this template file. We will not use your e-mail address for any other purpose.” & vbCrLf & vbCrLf & _
“NOTE: If you click Yes, you will asked to allow this program to send the e-mail. Click Yes when prompted.”, vbQuestion + vbYesNo + vbDefaultButton1) = vbNo Then
Exit Sub
End If
‘Register the user.
On Error Resume Next
Set olApp = GetObject(, “Outlook.Application”)
If Err.Number = 429 Then ‘Outlook was not open…
Set olApp = CreateObject(“Outlook.Application”)
End If
On Error GoTo 0
Set olNameSpace = olApp.GetNamespace(“MAPI”)
olNameSpace.Logon ProfileName, PasswordIfAny, blnShowDialog, False
Set msgItem = olApp.CreateItem(CurrentItem)
‘Set msgItem = olApp.ActiveInspector.CurrentItem
With msgItem
.To = “emailaddress@mailserver.com”
.Subject = “Register: ” & FileNameOnly & ” – ” & DOTversion
.Send
End With
Set msgRecip = Nothing
Set msgItem = Nothing
Set olApp = Nothing

End Sub

Let me know your thoughts. Thanks again!!
Troy

Reply | Quote

Troy,

I’ll jump in here with a bit of advice. Jefferson mentioned the drawbacks to using the Outlook object model to send email from a vb or vba (or any client) app. Those are indeed drawbacks that frustrated me until I explored the alternatives. I suggest you do the same in order to get around the error you post here and others that are sure to crop up in the future.. If your app will run at a site with Exchange Server, you can use the SMTP engine on it (using the CDONTS objects) or as others have done, use the SMTP engine called “Redemption” (I think that’s the name. Charlotte knows.) There are other object libraries available too, for free. Post back if you are interested in a free one.

Reply | Quote

Kevin, I’m interested in knowing where to get a free object library for email from vba … especially if you can vouch for its usefulness.
Gwenda

Reply | Quote

Check out wsInetTools V.3B at http://www.winscripter.com[/url%5D. I’ve not used it, but know the author who vouche for it’s effectiveness. I use the CDONTS technique for now.

Reply | Quote

Tell me more about this CDONTS. I have to distribute this procedure to multiple PCs, all running Office 2000. I’d rather set a reference that would be the same for all these machines than have to distribute an extra file that I would have to explain and hope it is used correctly. Is CDONTS another library included with Office 2000? I couldn’t find it by that name.

I actually don’t mind setting the reference at this point, since this seems to work well if they have Outlook running. If I have to, I’ll put an error handler in place to tell them to start over after they get Outlook running. That seems the only drawback right now to what I have.

Thanks!!
Troy

Reply | Quote

http://www.powerasp.com/content/hintstips/asp-email.asp%5B/url%5D is a good quick lesson.
http://www.cdolive.com/asp1.htm%5B/url%5D is helpful.
http://msdn.microsoft.com/library/default…./html/email.asp[/url] ain’t bad either.

If you’ve got an ISP running SMTP that supports ASP (we use the SMTP service in Exchange, but IIS runs the script), you can send mail from any script or app (vb/vba, etc.)

Your app contains the call to the .asp using the IE object model:

For Example:
Sub EMailNotice(strSubject As String, strBody As String, Optional strTo As String, Optional strCC As String)
#Const IniDEV = False
#If IniDEV Then
Dim oIE As InternetExplorer
#Else
Dim oIE As Object
#End If

Dim sFrom As String
Dim sUrl As String
Dim sFileName As String

‘GetUserEmailAddress
sFrom = CurrentUserEmailAddress ‘(this function pulls the user’s SMTP address from the Exchange server database)

If sFrom = “” Then
sFrom = “me@mydomain.com”
End If

If oFso.FolderExists(HOMEFOLDER) Then
sUrl = “http://hqss01/perbcentral/cdomail.asp?”
Else
sUrl = “http://www.perb.ca.gov/perbcentral?” ‘This adds support for users outside the VPN/LAN
End If

If strTo = “” Then
strTo = ITDEVELOPER
End If

sUrl = sUrl & “to=” & strTo & “&cc=” & strCC & “&from=” & sFrom

sUrl = sUrl & “&subject=” & strSubject & “&body=” & strBody

‘Debug.Print sUrl

Set oIE = CreateObject(“InternetExplorer.Application”)
With oIE
.Visible = False
.Navigate sUrl
DoEvents
‘Don’t destroy the object till the “page” displays i.e. the message is sent
Do
‘gotta wait till the page loads and processes.
‘If early binding, this should be faster
Loop Until .StatusText Like “Done*”
.Quit
End With
Set oIE = Nothing

End Sub

The asp script looks like this:

<%
Option Explicit

Dim oNewMail
Dim sFrom
Dim sTo
Dim sSubject
Dim sBody
dim sCC

sFrom=Request.querystring("from")
sTo=Request.querystring("to")
sSubject=Request.querystring("subject")
sBody=Request.querystring("body")
sCc=Request.querystring("cc")

response.write "From: " & sfrom & "
"
response.write "To: " & sTo & "
"
response.write "Subject: " & sSubject & "
"
response.write "Body: " & sBody & "
"
response.write "CC: " & sCc & "
"

Set oNewMail = Server.CreateObject("CDONTS.NewMail")

oNewMail.From = sFrom
oNewMail.To = sTo
if sCC “” then
oNewMail.cc = sCC
end if
oNewMail.Subject = sSubject
oNewMail.Body = sBody
oNewMail.Send

Set oNewMail = Nothing

%>

You need to set no references in your app and you don’t need to distribute any dlls or ocxes. Hope you find this useful. I love it.

Reply | Quote

Kevin,
Thanks for all the information. I must admit some aspects of it are a little over my head.

For example, what do I do with this asp script? I’ve never worked with asp scripts before.

This almost seems like using a nuke to get rid of an ant hill. All I need is to send a simple message (actually just the subject) to a single static address. I’m using it as a way to register the installation of a global template. That way I know who to send updates to at a later time. Maybe this is simpler than I think it is. Please don’t take this the wrong way. I really appreciate this info and may use it in the future if my e-mailing needs become more complex. If you disagree that this may be overkill for my needs (which I don’t think I made clear previously), please let me know and why.

That’s why at this point, I think I’ll stay with setting the reference to the Outlook library unless you convince me otherwise.

Thanks again for all the help!! I’ll definitly file your information away for future use.
Troy

Reply | Quote

Well, don’t be intimidated. Wait till your users see the “Warning!” message from Outlook if you use the Outlook objects. Now that’s intimidating! And which profile do you load? What if the profile you pick does not correspond to the Windows user logged in. Now your code needs to provide or prompt for a password. Oi!

Believe me, I went down the road you now travel. I had to disable all the emailing features from my app until I discovered CDONTS, Redemption and the Winscripter SMTP engine. I chose CDONTS because I don’t want to deal with another dll. (I may change my mind after testing the Winscripter tool).

So, this may seem like a pain, but once up and running, it’s very very smooth.

To answer your question, you put the .asp script into a folder on your IIS server under the wwwroot folder. Then just address the file in the url as shown in my code. Too easy.

Reply | Quote

If the “Warning” message you talk about is the warning that the program is sending the file, then I’ve already got a message “Warning” them of this warning.

Most users don’t have but one profile. If they have more than one, the code I have brings up the dialog box where they can choose their profile. If they are connected to the network, they don’t require a password because their password is given when they long into windows that affects Outlook and other applications.

I haven’t tested the latest version of my code when connecting via VPN, but so far everything is working in my tests at work.

With regard to the IIS server, convincing someone that I need access to that to run a VBA program would be like getting a write act from Congress. It is managed for our company by another company along with the rest of our IT features and believe me when I say it would be next to if not impossible to convince them to do this. If I get desperate, I may try, but I won’t be very optimistic.

Thanks again for the info!!
Troy

Reply | Quote

One more note: (from the Jonny Papa article):

Using the CDONTS.NewMail object is an easy and powerful technique to solve the problem of email-enabling your Web applications. You can even attach files to messages using the AttachFile method. This is a quirky solution because developers using Windows

Reply | Quote

Redemption isn’t an SMTP library. It uses Extended MAPI rather than CDO and the regular MAPI calls. Redemption is not free, but it isn’t expensive either, and it is redistributable.

Reply | Quote

You see. I knew you knew. Thanks for the concise explanation and clarification.

Reply | Quote

Let’s start with this one:

Run-time error ‘-2079063791 (84140111):
The server is unavailable. Contact the system administrator if this condition persists.

Reply | Quote

OK, you caught me sleeping. I obviously didn’t look close enough to see that those were variables. Your workaround code worked great, EXCEPT it weirded me out. Why? Because I now saw a prompt for the profile, and the mail message was sent, but I never saw Outlook open. I have Windows 2000. I could see Outlook.exe in the Processes tab while the message was being created and then sent, but did NOT see Outlook in the Applications tab. After the e-mail was sent, Outlook.exe disappeared from the Processes tab. Actually, I think that is great, but I’m wondering two things:

1. Was this really what was supposed to happen?

2. Given what happens, do you think Joe Blow user would be wierded out by not seeing Outlook open? If so, how would I open it and keep it open?

I didn’t get a chance to check to see if this fixed the VPN issues yet. I’ll let you know. However, I was curious what you meant by “start Outlook the old-fashioned way, using the Shell, and then using GetObject again. ” I’ve never used “the Shell”, but I’m assuming you mean the Shell function. If I did this, is there an automated way to find the path to Outlook before I have to pass that to the function?

Thanks again for all the help!!
Troy

Reply | Quote

I’m not sure you need to know the full path. My gut feeling is, if you can type “outlook.exe” in the Start|Run… dialog, it should work with the Shell.

I was able to do this in the Immediate window in the VBE from Word:

shell “outlook.exe”
set obj1 = getobject(,”Outlook.Application”)
obj1.quit

It worked! There might, however, be a timing problem when you do this in code, during the several seconds that Outlook requires to load. For suggestions on handling that, search the Lounge for info on Sleep (the API call, not dozing).

Reply | Quote

If I get feedback about it, I’ll implement this last suggestion. However, I think at this point I’m going to leave well enough alone.

By the way the code worked great in the VPN situation as well, so I think I’m set for now.

Thanks again for all the help!!
Troy

Reply | Quote