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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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