MS Outlook Automation (1 Viewer)

[Long Ball Larry]

I have a list of 71 clients in Excel. Each has at least one cell with an e-mail address, some with 2 or 3 columns with e-mail addresses.

I have reports that need to be e-mailed to them on a quarterly basis that are saved on our network.

The e-mails need to have specific subjects with the client's name and the report date. The body of the e-mail will need a few variables, such as the report date, but will be otherwise pretty static.

How can I automate this so that the correct report gets attached to the correct client e-mail?

The manual way that this is being done currently is way too clunky.

TIA.

 

[Brony]

Here's the VBA code I use to do this. In my spreadsheet,

Column A = 1 or 0 to turn on/off that row

Column B = territory

Column F = Email address

Column J = CC address

Column N = attachment directory and file name ("c:\temp\report Chicago.xlsx")

I put a pop-up in there to make sure I didn't send out 20 reports that were messed up.

Sub Send_Files()
'Working in 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range, FileCell As Range, rng As Range
Dim sMsgBody As String
With Application
.EnableEvents = False
.ScreenUpdating = False
End With

On Error Resume Next

Set sh = Sheets("MailList")
CurrentMonth = Range("DataMonth").Value
CurrentMonthTx = Format(CurrentMonth, "MMM YYYY")

Set OutApp = CreateObject("Outlook.Application")

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

Set rng = sh.Cells(cell.Row, 1).Range("A1:L1")

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

If cell.Offset(0, -5).Value = 0 Then
GoTo 20
End If

With OutMail
.To = cell.Value
.Cc = cell.Offset(0, 4).Value
.Subject = "Updated Territory Target Lists Q1 2014 for " & cell.Offset(0, -4).Value
sMsgBody = "Greetings," & vbCr
sMsgBody = sMsgBody & "Attached is an extract of the customers for the " & cell.Offset(0, -4).Value & " territory." & vbCr
sMsgBody = sMsgBody & "Please let me know if you discover anything in error. " & vbCr & vbCr
.Body = sMsgBody

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
'prompt
strPrompt = "Are we OK to continue?"
strTitle = "Check " & cell.Offset(0, -4).Value
Answer = MsgBox(strPrompt, vbOKCancel, strTitle)
If Answer = vbCancel Then Exit Sub


.Send 'Or use Display
End With

Set OutMail = Nothing
End If
20 Next cell

Set OutApp = Nothing

With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

 

[Cliff Clavin]

I have a list of 71 clients in Excel. Each has at least one cell with an e-mail address, some with 2 or 3 columns with e-mail addresses.

I have reports that need to be e-mailed to them on a quarterly basis that are saved on our network.

The e-mails need to have specific subjects with the client's name and the report date. The body of the e-mail will need a few variables, such as the report date, but will be otherwise pretty static.

How can I automate this so that the correct report gets attached to the correct client e-mail?

The manual way that this is being done currently is way too clunky.

TIA.

VBS or Powershell script set to run as a scheduled task.

 

[Long Ball Larry]

Probably a stupid question, but can a VBA script in Excel trigger any other MS program then? Is it limited to MS programs?

i've have not written much VBA code, but we use some VBA code that others have developed for certain processes in Excel.

After going through the above code to make sure that all of the references are accurate for me, then I run the script in Excel and it generates the outlook messages?

 

[Cliff Clavin]

Probably a stupid question, but can a VBA script in Excel trigger any other MS program then? Is it limited to MS programs?

i've have not written much VBA code, but we use some VBA code that others have developed for certain processes in Excel.

After going through the above code to make sure that all of the references are accurate for me, then I run the script in Excel and it generates the outlook message

You're not automating Outlook. VBA/VBS/PHP or any other scripting language can send through SMTP. You could run the script above without having Outlook installed.

 

[Brony]

Probably a stupid question, but can a VBA script in Excel trigger any other MS program then? Is it limited to MS programs?

i've have not written much VBA code, but we use some VBA code that others have developed for certain processes in Excel.

After going through the above code to make sure that all of the references are accurate for me, then I run the script in Excel and it generates the outlook messages?

I have that set to run as a macro - I click a button and it runs. Make sure you do a test one with only your personal email address in the spreadsheet. Trust me on this.

I know that VBA can trigger some other applications, but I don't have experience with what it can and can't outside of Excel/Powerpoint/Outlook

Also note that I tried to figure out how to add in pictures to the email body instead of attachments for the sake of those on mobile devices, but I could never get this to work with my limited knowledge of VBA.

 

[Patriotsfatboy1]

Can't you use mail merge functionality in Office for this?

http://office.microsoft.com/en-us/word-help/use-mail-merge-to-send-personalized-e-mail-messages-to-your-e-mail-address-list-HA010109162.aspx

 

[Long Ball Larry]

Can't you use mail merge functionality in Office for this?

http://office.microsoft.com/en-us/word-help/use-mail-merge-to-send-personalized-e-mail-messages-to-your-e-mail-address-list-HA010109162.aspx

I was wondering about this, but I'm not sure if I see where the attachment piece comes in. can I have a file location as one of the pieces of data in the merge file and then it will upload the correct attachment?

 

[Long Ball Larry]

Probably a stupid question, but can a VBA script in Excel trigger any other MS program then? Is it limited to MS programs?

i've have not written much VBA code, but we use some VBA code that others have developed for certain processes in Excel.

After going through the above code to make sure that all of the references are accurate for me, then I run the script in Excel and it generates the outlook message

You're not automating Outlook. VBA/VBS/PHP or any other scripting language can send through SMTP. You could run the script above without having Outlook installed.

hmm, interesting. Thanks.

 

[captain_amazing]

I have a list of 71 clients in Excel. Each has at least one cell with an e-mail address, some with 2 or 3 columns with e-mail addresses.

I have reports that need to be e-mailed to them on a quarterly basis that are saved on our network.

The e-mails need to have specific subjects with the client's name and the report date. The body of the e-mail will need a few variables, such as the report date, but will be otherwise pretty static.

How can I automate this so that the correct report gets attached to the correct client e-mail?

The manual way that this is being done currently is way too clunky.

TIA.

VBS or Powershell script set to run as a scheduled task.

This if you want it to be fully automated without having to actually open the file or run a macro manually.

 

[Long Ball Larry]

Probably a stupid question, but can a VBA script in Excel trigger any other MS program then? Is it limited to MS programs?

i've have not written much VBA code, but we use some VBA code that others have developed for certain processes in Excel.

After going through the above code to make sure that all of the references are accurate for me, then I run the script in Excel and it generates the outlook messages?

I have that set to run as a macro - I click a button and it runs. Make sure you do a test one with only your personal email address in the spreadsheet. Trust me on this.

I know that VBA can trigger some other applications, but I don't have experience with what it can and can't outside of Excel/Powerpoint/Outlook

Also note that I tried to figure out how to add in pictures to the email body instead of attachments for the sake of those on mobile devices, but I could never get this to work with my limited knowledge of VBA.

yeah, I would definitely test it. thanks for that script.

 

[Long Ball Larry]

I have a list of 71 clients in Excel. Each has at least one cell with an e-mail address, some with 2 or 3 columns with e-mail addresses.

I have reports that need to be e-mailed to them on a quarterly basis that are saved on our network.

The e-mails need to have specific subjects with the client's name and the report date. The body of the e-mail will need a few variables, such as the report date, but will be otherwise pretty static.

How can I automate this so that the correct report gets attached to the correct client e-mail?

The manual way that this is being done currently is way too clunky.

TIA.

VBS or Powershell script set to run as a scheduled task.

This if you want it to be fully automated without having to actually open the file or run a macro manually.

I would like to look further into that, but for now, opening a file and running a macro would be a big improvement and probably a good incremental step.

 

[Thorpe]

Probably a stupid question, but can a VBA script in Excel trigger any other MS program then? Is it limited to MS programs?

i've have not written much VBA code, but we use some VBA code that others have developed for certain processes in Excel.

After going through the above code to make sure that all of the references are accurate for me, then I run the script in Excel and it generates the outlook message

You're not automating Outlook. VBA/VBS/PHP or any other scripting language can send through SMTP. You could run the script above without having Outlook installed.

If you are talking about the script Brony posted, then you are wrong. You need the Outlook App installed

Set OutApp = CreateObject("Outlook.Application")

It can be done without Outlook. I would recommend the CDO method. Google "vba script email CDO"

 

[Long Ball Larry]

Probably a stupid question, but can a VBA script in Excel trigger any other MS program then? Is it limited to MS programs?

i've have not written much VBA code, but we use some VBA code that others have developed for certain processes in Excel.

After going through the above code to make sure that all of the references are accurate for me, then I run the script in Excel and it generates the outlook message

You're not automating Outlook. VBA/VBS/PHP or any other scripting language can send through SMTP. You could run the script above without having Outlook installed.

hmm, interesting. Thanks.

so would the e-mail messages be in my sent items (if I run the macro) or not? I need to have the messages saved somewhere.

 

[Patriotsfatboy1]

Can't you use mail merge functionality in Office for this?

http://office.microsoft.com/en-us/word-help/use-mail-merge-to-send-personalized-e-mail-messages-to-your-e-mail-address-list-HA010109162.aspx

I was wondering about this, but I'm not sure if I see where the attachment piece comes in. can I have a file location as one of the pieces of data in the merge file and then it will upload the correct attachment?

I am not sure if Outlook 2013 introduced this ability or not. You could not do attachments out of the box in 2010.

You could set up a cloud share and just point them to a link on something like Google Drive or Dropbox.

 

[Cliff Clavin]

Probably a stupid question, but can a VBA script in Excel trigger any other MS program then? Is it limited to MS programs?

i've have not written much VBA code, but we use some VBA code that others have developed for certain processes in Excel.

After going through the above code to make sure that all of the references are accurate for me, then I run the script in Excel and it generates the outlook message

You're not automating Outlook. VBA/VBS/PHP or any other scripting language can send through SMTP. You could run the script above without having Outlook installed.

If you are talking about the script Brony posted, then you are wrong. You need the Outlook App installed

Set OutApp = CreateObject("Outlook.Application")

It can be done without Outlook. I would recommend the CDO method. Google "vba script email CDO"

No, not with Brony's macro. Via powershell or VBS.

 

[Bottomfeeder Sports]

Probably a stupid question, but can a VBA script in Excel trigger any other MS program then? Is it limited to MS programs?

i've have not written much VBA code, but we use some VBA code that others have developed for certain processes in Excel.

After going through the above code to make sure that all of the references are accurate for me, then I run the script in Excel and it generates the outlook message

You're not automating Outlook. VBA/VBS/PHP or any other scripting language can send through SMTP. You could run the script above without having Outlook installed.

If you are talking about the script Brony posted, then you are wrong. You need the Outlook App installed

Set OutApp = CreateObject("Outlook.Application")

It can be done without Outlook. I would recommend the CDO method. Google "vba script email CDO"

And depending on security settings that you may or may not have any control over automating Outlook might result is non stop security "do you want to allow this" type messages. Use CDO as suggested here.