Forum Discussion
Sending Outlook mail using Excel VBA
I have a VBA macro working - mostly - that copies part of an Excel worksheet and pastes (with formatting) into a new Outlook email, between surrounding text blocks.
Trouble is, the macro is not reliable. We take as a given that Outlook is running. The macro fails if it is not, but that's acceptable for the environment the macro is used in.
The macro works fine if Outlook is running with no windows open other than the inbox. It always fails if another email is open, whether the Inbox or that email is frontmost. In the case where an email is already open, the new email is created with the proper subject and such but the clipboard gets pasted into the email that was already open before the macro was called. [correction] The only scenario that works as intended is when Outlook is running, not minimized, and no document windows open.
How can I make sure the active selection point in Outlook is placed into the macro-created email?
Here's my code. Not shown is the Excel code that has already copied the desired content to the clipboard.
Dim strbody1 As String
Dim strbody2 As String
Dim objMail As Outlook.MailItem
Dim objInsp As Inspector
Dim objDoc As Word.Document
Dim objSel As Word.Selection
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
strbody1 = “Leading text appears before Excel table“
strbody2 = “Following text appears after Excel table“
' Opens email and adds subject and text
Set objMail = Outlook.Application.CreateItem(0)
With objMail
.Display
.Subject = “My Subject”
End With
'Pastes table to email
Set objInsp = objMail.GetInspector
If objInsp.EditorType = olEditorWord Then
Set objDoc = objInsp.WordEditor
Set objWord = objDoc.Application
Set objSel = objWord.Selection
With objSel
.InsertAfter vbCr & strbody2
.HomeKey unit:=wdStory
.PasteAndFormat (wdformatorginalformatting)
.EndOf unit:=wdStory, Extend:=wdMove
.HomeKey unit:=wdStory
.InsertAfter strbody1 & vbCr
End With
End If
Set objMail = Nothing
18 Replies
Wayne-
How Exciting!! Glad you were able to get it working! I'll have to remember that for future reference!
Cheers!
Wayne-
In order to avoid issues you can first check to see if Outlook is open, like shown on Ron De Bruin's website here:
https://www.rondebruin.nl/win/s1/outlook/openclose.htm
Sub TestOutlookIsOpen() Dim oOutlook As Object On Error Resume Next Set oOutlook = GetObject(, "Outlook.Application") On Error GoTo 0 If oOutlook Is Nothing Then MsgBox "Outlook is not open, open Outlook and try again" Else 'Call NameOfYourMailMacro End If End SubIf you incorporate this into the code it may help resolve your issue. If you still have issues, feel free to post back and I can help you incorporate the snippet into your code.
Wayne-
Sorry I think I misunderstood your issue. I haven't fiddled around with Outlook VBA in a while, but maybe try changing this line:
Set objInsp = objMail.GetInspector.CurrentItem
If that doesn't work try these two modifications:
Add this to your Dim Statements:
Dim outApp As Outlook.Application
Then change this code as well:
Set objInsp = outApp.ActiveExplorer.CurrentItem
If neither of these will work. Please let me know and I can do additional testing to try and replicate your issue.
