email

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a code that sends an excel worksheet/workbook to a recipient. but the
problem lies when the user does not have microsoft open to email the sheet.
the email just sits in the outbox waiting for the user to start the
application and then sends it out - sometimes days later; is there a code
that can be used in excel (script) to open microsoft outlook and acutally
email it also?
 
This code hooks into Outlook if it's already open or opens it and quits if it
is not open, sending the e-mail immediately in both cases. I'm not sure if
you're automating Excel's SendMail method or not, but if you are you should
recode to automate the Outlook Object Model instead.

Dim objOL As Outlook.Application, blnNewOutlook As Boolean
Dim objExp As Outlook.Explorer
Dim objNS As Outlook.NameSpace
Dim objMail As Outlook.MailItem

'Retrieve an existing Outlook Application object if it is already loaded
Set objOL = GetObject(, "Outlook.Application")
If objOL Is Nothing Then
'Outlook is closed; open it
Set objOL = New Outlook.Application
blnNewOutlook = True
End If
Set objNS = objOL.GetNamespace("MAPI")

If blnNewOutlook = True Then
Set objExp =
objOL.Explorers.Add(objNS.GetDefaultFolder(olFolderInbox),
olFolderDisplayNormal)
objExp.Activate 'Show Outlook
End If

Set objMail = objOL.CreateItem(olMailItem)
objMail.Subject = "test"
objMail.To = "(e-mail address removed)"
objMail.Send

Set objMail = Nothing

If blnNewOutlook = True Then
objExp.Close
objOL.Quit
End If

Set objExp = Nothing
Set objNS = Nothing
Set objOL = Nothing
 
Back
Top