Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: VBA to open Outlook?

  1. #1
    Board Regular
    Join Date
    Oct 2005
    Location
    Northants, UK
    Posts
    945
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to open Outlook?

    If I am using sendmail in a simple macro, is there any code I can add to actually open Outlook?

    Some users of the macro are pressing the macro button and do not have Outlook open so the email isn't sent until they eventually open Outlook!

    Thanks in advance

  2. #2
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to open Outlook?

    Hi

    perhaps something like:

    Code:
    Dim obj
     
    On Error Resume Next
    Set obj = GetObject(,"Access.Application")
    On Error Goto 0
    If obj Is Nothing Then Set obj = CreateObject("Access.Application")
    obj.Visible = True
    Richard Schollar

    Using xl2013

  3. #3
    Board Regular
    Join Date
    Oct 2005
    Location
    Northants, UK
    Posts
    945
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to open Outlook?

    Thanks for the reply

    Being a VB numptie, how can I combine your code with mine;



    Sub Email()

    On Error GoTo email_error
    ActiveWorkbook.SendMail Array("email address"), "Subject line"
    email_error:
    End Sub

  4. #4
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to open Outlook?

    Should be as simple as:

    Code:
    Sub Email()
    Dim obj
    
    On Error Resume Next
    Set obj = GetObject(,"Access.Application")
    On Error Goto 0
    If obj Is Nothing Then Set obj = CreateObject("Access.Application")
    obj.Visible = True
    
    On Error GoTo email_error
    ActiveWorkbook.SendMail Array("email address"), "Subject line"
    email_error:
    End Sub
    Richard Schollar

    Using xl2013

  5. #5
    Board Regular
    Join Date
    Oct 2005
    Location
    Northants, UK
    Posts
    945
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to open Outlook?

    Tried it and it fails - it highlights "If obj is nothing..........."

    When you say "Access.Application" is that actually what has to be typed, or should I replace it with something else Outlook related?

    Told you I was a VB numptie..............

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,490
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA to open Outlook?

    Replace 'Access' with 'Outlook'

  7. #7
    Board Regular
    Join Date
    Oct 2005
    Location
    Northants, UK
    Posts
    945
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to open Outlook?

    I now get "Run time error 438"

    "Object doesn't support this property or method"

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,490
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA to open Outlook?

    Oh, yes - remove the obj.Visible = True line.

  9. #9
    Board Regular
    Join Date
    Oct 2005
    Location
    Northants, UK
    Posts
    945
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to open Outlook?

    It now sends the email ok when Outlook is already open, but returns this error when Outlook is not already open;

    "Runtime Error 424"
    "Object Required"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •