VBA to send Outlook email difficulties

banneduser123

Banned - Rules violations
Joined
Mar 29, 2006
Messages
181
Hi,

I'm trying to use this macro to send an email. Let me try to explain:

1. need email to include default Outlook signature
2. want to be able to multi-task in Outlook

When I use this macro, it composes a new email.
When the email window pops up, i'm not able to click on any other Outlook windows. i'm restricted to finishing this new email before i can do anything else in Outlook.
Also, the new email doesn't include my assigned Outlook signature.

I hope that was clear.
I'm wondering where in my code it creates this behavior? Or how i can fix it?
Thanks

**code below:


VBA Code:
[Sub CD_Prep()

 
    Dim OL As New Outlook.Application

    Dim olMail As Outlook.MailItem
    Set olMail = OL.CreateItem(olMailItem)

    Dim SrcSheet As Excel.Worksheet
    Set SrcSheet = Sheets("Notes")

    With olMail
        .To = SrcSheet.Range("A55").Text
        .CC = SrcSheet.Range("B55").Text
        .Subject = SrcSheet.Range("C55").Text
        .Body = SrcSheet.Range("D55").Text
        .Display vbModal
        '.Send
    End With


    Set OL = Nothing
End Sub

/CODE]
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,980
you told it vbMODAL, that means you cannot do anything else until that window closes.
remove the vbModal.

.Dislpay TRUE may be the same. so remove display.
 

banneduser123

Banned - Rules violations
Joined
Mar 29, 2006
Messages
181
perfect. thanks a lot for the help

I had to keep the line as
.Display
but i think it's doing as i need...thanks again
 

banneduser123

Banned - Rules violations
Joined
Mar 29, 2006
Messages
181
ended up using this and it serves my purposes:

VBA Code:
Sub CD_Prep()

    
    Dim OApp As Object, OMail As Object, signature As String

    Set OApp = CreateObject("Outlook.Application")
    Set OMail = OApp.CreateItem(0)

    Dim SrcSheet As Excel.Worksheet
    Set SrcSheet = Sheets("Notes")

With OMail
.Display
End With

signature = OMail.Body

With OMail
        .To = SrcSheet.Range("A55").Text
        .CC = SrcSheet.Range("B55").Text
        .Subject = SrcSheet.Range("C55").Text
        .HTMLBody = "<font style=""font-family: Calibri; font-size: 11pt;""/font>" & "Hi, " & "</p>" & .HTMLBody & vbNewLine & signature
        .Display
        '.Send
    End With



Set OMail = Nothing
Set OApp = Nothing
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,170
Messages
5,640,568
Members
417,151
Latest member
ChickenTenderer

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