4

everyday I am handling daily reporting. Which was quite time consuming. Basically I need to send email containing brief comparison of sales yesterday with sales last week and month. That is working pretty well. Once this is done the message is pasted to new sheet and then I have to copy it and paste into new email in Outlook.

Is there a possibility to create macro that will open new message in Outlook? So I'll be able to insert my text. I am able to write macro that will send it directly from Excel but this is not something I really want to as some part of the reporting must by done by looking at numbers manually.

Many thanks in advance!

pnuts
  • 58,317
  • 11
  • 87
  • 139
Petrik
  • 823
  • 2
  • 12
  • 25

3 Answers3

8

To add the ActiveWorbook as an attachment:

  1. Save it to a specifc location
  2. Use Attachments.Add to add the file from the location from 1

code

Sub CustomMailMessage()
Dim strFile As String
Dim OutApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Recipient
Dim Recipients As Recipients

  Set OutApp = CreateObject("Outlook.Application")
  Set objOutlookMsg = OutApp.CreateItem(olMailItem)

  strFile = "C:\temp\myfile.xlsx"
  ActiveWorkbook.SaveAs strFile

  Set Recipients = objOutlookMsg.Recipients
  Set objOutlookRecip = Recipients.Add("alias@domain.com")
  objOutlookRecip.Type = 1

  With objOutlookMsg
    .SentOnBehalfOfName = "sales@domain.com"
    .Subject = "Testing this macro"
    .HTMLBody = "Testing this macro" & vbCrLf & vbCrLf
    'Resolve each Recipient's name.
    For Each objOutlookRecip In objOutlookMsg.Recipients
      objOutlookRecip.Resolve
    Next
    .Attachments.Add strFile
    .display
  End With

  'objOutlookMsg.Send
  Set OutApp = Nothing
End Sub
Rdster
  • 1,846
  • 1
  • 16
  • 30
brettdj
  • 54,857
  • 16
  • 114
  • 177
6

I've found this one and it is working perfectly!!!!

Just maybe one extra thing - is there a possibility to attach opened document as a attachment? 

Sub CustomMailMessage()
Dim OutApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Recipient
Dim Recipients As Recipients

  Set OutApp = CreateObject("Outlook.Application")
  Set objOutlookMsg = OutApp.CreateItem(olMailItem)

  Set Recipients = objOutlookMsg.Recipients
  Set objOutlookRecip = Recipients.Add("alias@domain.com")
  objOutlookRecip.Type = 1

  objOutlookMsg.SentOnBehalfOfName = "sales@domain.com"
  objOutlookMsg.Subject = "Testing this macro"
  objOutlookMsg.HTMLBody = "Testing this macro" & vbCrLf & vbCrLf
  'Resolve each Recipient's name.
  For Each objOutlookRecip In objOutlookMsg.Recipients
    objOutlookRecip.Resolve
  Next
  'objOutlookMsg.Send
  objOutlookMsg.Display

  Set OutApp = Nothing  
End Sub
Rdster
  • 1,846
  • 1
  • 16
  • 30
Petrik
  • 823
  • 2
  • 12
  • 25
4

I cannot test it now, but it would go like this:

set o = createObject("Outlook.Application")
set m = o.CreateItem(olMailItem) ' replace it with 0 if you get error here
o.show ' or .Display - not sure

You can set o.To, o.Subject etc. before displaying it. Sorry it is not tested but I do not have Outlook on my home computer, I use it only at work. I will check it tomorrow if I remembered correctly.

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
avb
  • 1,743
  • 1
  • 13
  • 23
  • there is error on the second line - "Object do not support this property or method. – Petrik Mar 30 '14 at 19:06
  • + 1 You might want to declare your variables as well? :) – Siddharth Rout Mar 31 '14 at 04:38
  • I checked already, it should be `o.Display`. Error is probably caused by Excel not knowing Outlook enums (try 0 instead of `olMailItem`). Siddhart, you are right (thanks for editing my 'Add' error), but declaring variables (although is a good practice) is not necessary so one does and one deos not (I do) – avb Mar 31 '14 at 06:40