Open new email from Excel

[lesley burlingham]

Hi

I would like to have a button on my form which opens a new email message - I want the destination name populated and the subject to have details from the spreadsheet.

I don't want the email to send automatically.

I have the following.

Sub SendMeFromExcel()
    Dim EmailTo As String
    Dim oApp As Object
    Dim oItem As Object
   
    On Error GoTo ErrorHandler
   
    EmailTo = "lesley-ann.burlingham@cnhind.com"
   
    Set oApp = CreateObject("Outlook.Application")
    Set oItem = oApp.CreateItem(0)
   
    With oItem
        .To = EmailTo
        .Subject = "This is a test.  This email is being sent from Excel!!!"
        .Body = "You can put the body too"
        .Importance = 1
        .Send
    End With
   
    GoTo ExitSub
   
ErrorHandler:
    MsgBox "There was an error."
   
ExitSub:
    Set oItem = Nothing
    Set oApp = Nothing
End Sub

 

[Deepak]

Hi,

For Subject

.Subject = Range("A1")

& To don't auto send

Change .Send to .Display

 

[lesley burlingham]

Wonderful, if I wanted to have texted then a cell value how would I right this.

 

[Deepak]

Is this..

"ABC" & Range("A1")

 

[lesley burlingham]

Hi. Thank for your help with my subject. I need to have _ between each work and cell. I can't get my Vb to accept them.

 

[Deepak]

Hi,

I am not sure what u are looking for!!

To join the string in VBA use

"ABC" & "CD" & Range("A1") & "_" & "hello" & "how_" & "are" & "you!"

Or use Join(....., "-")