VBA to open Outlook?

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,048
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,048
Office Version
  1. 365
Platform
  1. Windows
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
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,048
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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..............
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,973
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Replace 'Access' with 'Outlook'
 

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,048
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I now get "Run time error 438"

"Object doesn't support this property or method"
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,973
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Oh, yes - remove the obj.Visible = True line.
 

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,048
Office Version
  1. 365
Platform
  1. Windows
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"
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,197
Messages
5,857,884
Members
431,905
Latest member
RW11700

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top