Forum Discussion

Anonymous's avatar
Anonymous
May 24, 2018

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 r...
Macros & VBA
Need Help
Matt Mickle's avatar
Matt Mickle
Bronze Contributor
May 24, 2018

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.

 

 

 

  • Anonymous's avatar
    Anonymous
    May 24, 2018

    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.

    • Matt Mickle's avatar
      Matt Mickle
      Bronze Contributor
      May 24, 2018

      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.

       

      • Anonymous's avatar
        Anonymous
        May 25, 2018

        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"

Resources