Using VBA in Excel to Open Outlook

Daniel C

New Member
Joined
Mar 3, 2006
Messages
10
Hi
Hope everyone that reads this is having a splendid day.
Unfortunately my knowledge of VBA isn't what it should be (at the moment)
If someone would be so kind as to tell what needs to be placed into
Private Sub Workbook_Open()
in order for Microsoft Outlook to open
that would be super.
Dan
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
thanks but.....

Thank you
the link was most informative however, it doesn't seem to cover what i'm after
I currently have this peice of code to search if Outlook is open
Private Sub Workbook_Open()
Dim Outlook As Object
On Error Resume Next
Set myout = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then MsgBox ("Your Outlook Email system is not running at the moment.")
On Error GoTo 0

However What i would like for it to do is to Open Outlook if Err.Number <> 0 rather than give them a message.
Dan
 
Upvote 0
hi dan.

sorry things didn't work out. see the code below for an example.

cheers. ben.

Code:
Option Explicit
Private Sub Workbook_Open()
'   Opens Outlook Application, creates and displays an email with activeworkbook as attachment
    
    Dim olkApp As Outlook.Application
    Dim olkNameSpace As Outlook.NameSpace
    Dim olkDefaultFolder As Outlook.MAPIFolder
    Dim olkMail As Outlook.MailItem
    
'   If Outlook isn't open, open it
    On Error Resume Next
    Set olkApp = GetObject(, "Outlook.Application")
    If Err.Number <> 0 Then Set olkApp = CreateObject("Outlook.Application")
    
    On Error GoTo 0
    
    Set olkNameSpace = olkApp.GetNamespace("MAPI")
    Set olkDefaultFolder = olkNameSpace.GetDefaultFolder(Outlook.olFolderInbox)
    Set olkMail = olkDefaultFolder.Items.Add(Outlook.olMailItem)
    
'   Use of CreateItem can cause run-time permission error -2044526587
    'Set olkMail = olkApp.CreateItem(Outlook.olMailItem)
    
'   Display mail message
    With olkMail
        .To = "bgmarston@gmail.com"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hi there"
        .Attachments.Add ActiveWorkbook.FullName
        .Display 'Use .Send to send
    End With
    
'   Release variables
    Set olkMail = Nothing
    Set olkDefaultFolder = Nothing
    Set olkNameSpace = Nothing
    Set olkApp = Nothing
    
End Sub
 
Upvote 0
Email Follow up

I tried using the code above and got a "User defined type not defined" compile error. Do I need and add-in?
 
Upvote 0

Forum statistics

Threads
1,214,646
Messages
6,120,717
Members
448,985
Latest member
chocbudda

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
Back
Top