Macro to populate email address and subject

My job involves using a system which generates an Outlook email and attachment to be sent to one of three addresses. The subject is a set format to which I have to add ID details. I'm looking for a macro to populate the email address and subject boxes. Any guidance much appreciated as I'm a total novice, thanks

Please try this..

This will help you in drafting the mail which will populate the Email address and subject and Body of you Email (You need to put all the details in excel) but you need to add the attachments manually.

Let me know if it works

Try this code


Sub Send_Files()
'Working in Excel 2000-2013
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range
Dim FileCell As Range
Dim rng As Range

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set sh = Sheets("Sheet1")

Set OutApp = CreateObject("Outlook.Application")

For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)

'Enter the path/file names in the C:Z column in each row
Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")

If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)

With OutMail
.to = cell.Value
.CC = "[email protected]"
.Subject = "Test Mail"
'.Body = "Hi " & cell.Offset(0, -1).Value

For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell) <> "" Then
If Dir(FileCell.Value) <> "" Then
.Attachments.Add FileCell.Value
End If
End If
Next FileCell

.Display 'Or use .Send
End With

Set OutMail = Nothing
End If
Next cell

Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

Hi Ravikanthreddy,

That is a cool macro, I am trying to achieve something similar (I have created a thread... here).

Just wondering if it is possible to have a macro for this which launches from Outlook, rather than Excel?