Send/display email through Excel in Outlook

[David77]

Hello everybody,

I am currently trying to create a macro in Excel that, once you click on it, displays a pre-written message (by me) and then allows the user to send off the email from their own account. Is this possible?

Furthermore, I know that you can do something like: Set.subject / Set.body of the email in order to write the pre-written message, however, is it possible to do this with spacing inbetween, so it's not just written in one big fat line? I want it to look like:

"Example
bla bla bla
bla bla"

Rather than:

"Example bla bla bla bla bla"

This is mostly because I have to create some bulletpoints in the email body, which would thus make it necessary to write with the line spacing inbetween.

If anybody knows of a macro and can share it here, so I can add it to my command button, that would be absolutely amazing!

Thank you very much everybody Your time is truly appreciated!

Best regards,
David

 

[Trevor G]

Hi David,

In the body after example use either vbCr or Chr(10) both mean carriage return

 

[David77]

Hi David,

In the body after example use either vbCr or Chr(10) both mean carriage return

Hello Trevor,

Thank you very much. What would the macro look like? Do you have an example I can edit or link to it?

 

[Trevor G]

Example below:

Sub SendMail()
'Set the reference to use Outlook
'Tools Menu >> References >> Search down for Microsoft Outlook
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)

With olMail
    .To = "email@google.com"
    .Subject = "Test"
    .Body = "Please see the following" & Chr(10) & _
    "The invoice is indivated below" & Chr(10) & Chr(10) & _
    "Regards" & Chr(10) & _
    "The sender"
    .Display 'Once tested change .Display to .Send
End With

olApp = Nothing
olMail = Nothing
End Sub

 

[David77]

Example below:

Sub SendMail()
'Set the reference to use Outlook
'Tools Menu >> References >> Search down for Microsoft Outlook
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)

With olMail
    .To = "email@google.com"
    .Subject = "Test"
    .Body = "Please see the following" & Chr(10) & _
    "The invoice is indivated below" & Chr(10) & Chr(10) & _
    "Regards" & Chr(10) & _
    "The sender"
    .Display 'Once tested change .Display to .Send
End With

olApp = Nothing
olMail = Nothing
End Sub

Hello Trevor,

Thank you so much!

I tried it, but nothing happens when I click on the command button?

 

[Trevor G]

Can you upload the code behind the button as I can't see what you have done. Have you set the reference in the VBA screen to use Outlook?

In VBA Tap the Tools Menu >> Tap References >> Search down the list for Microsoft Outlook and tap the box next to it and then tap OK.