Forum Discussion
Sending Outlook mail using Excel VBA
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 Sub
If 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.
I'm familiar with that excellent site, but it does not address the issue I'm having. (Or at least I can't find it.) I don't care if the macro fails when Outlook is not open. The folks using the macro use Outlook all day long.
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.
Sorry about the delay. I work on a Mac but this code has to be tested on a Windows machine.
Anyway, the .CurrentItem attempt returned a "type Mismatch" error. My tester reports that .CurrentView was on the list of suggestions but .CurrentItem was not.
The second attempt, the Dim statement and the objInspector line returned an error "object variable or with block variable not set"
Wayne-
No problem. I'll run some additional tests. However, before I do, I wanted to ask a few questions
I see MS Word VBA references in your code which makes me wonder:
1. If possible would you like to copy data from Excel directly to the email?
2. Are you trying to copy a table from word to the email?
3. Is word being used as some type of intermediary? If so what is the purpose?
The only reason I ask about these items is because there is a way to copy directly from excel and maintain formatting, but if you are trying to copy the table from word then the code will be different of course....