script to generate emails

[Bill_Davenport]

any suggestions on how i could do the following?

What i'd like to do is loop through each row, in a spreadsheet then pull the order number and generate an email to the correct department stating:

"Hello, the following order, (pull order number from order column), requires additional action by your department."

unfortunately these departments wants to receive an individual email for each order so i can't just email them my list for them to work on, something about how they work on their queue but i digress lol.

for example lets say i have two columns in my sheet that i want to work off of, Ordernumber and tech, the tech column will either be "Copper", "Copper bonded" or "GPON"

Copper needs to be emailed to copper@copper.fake
copper bonded needs to be emailed to copperbonded@copper.fake
and gpon need sto be emailed to gpon@gpon.fake

the concept seems simple but i'm not sure how to program it Even if i have to generate 1 email at a time and it just loops through making me send each one is better than doing it manually every day.

thanks in advance for any advice.

 

[MrKowz]

This assumes you use Outlook for your email client. Before running this code, be sure you have the reference for Microsoft Outlook #.# Object Library enabled. You can do this by opening the VBA editor and going to Tools > References. #.# just represents the version (in Office 2010+, this should be 15.0, I believe).

Also, this code is UNTESTED, so please ensure you test it on a sample of the data and back up your file before running.

Hope this helps!

Public Sub CreateOutlookEmail_BillDavenport()
Dim OutApp          As Object, _
    OutMail         As Object
    
Dim strbody         As String

Dim i               As Long, _
    LR              As Long

'   NOTE: Must have references to the following libraries enabled:
'       * Microsoft Outlook 15.0 Object Library

LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    
    
    'Build string to enter into email body
    strbody = "Hello, the following order, " & Range("B" & i).Value & ", requires additional action by your department."
                
    
    On Error Resume Next
    With OutMail
        .To = Range("A" & i).Value & "@copper.fake"
        .CC = ""
        .BCC = ""
        .Subject = "Additional action requested - Order# " & Range("B" & i).Value
        .HTMLBody = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        '.Send
        .Display
    End With
    
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing
Next i

End Sub

 

[Bill_Davenport]

great I will test it right now and le tyou know.

 

[Bill_Davenport]

This assumes you use Outlook for your email client. Before running this code, be sure you have the reference for Microsoft Outlook #.# Object Library enabled. You can do this by opening the VBA editor and going to Tools > References. #.# just represents the version (in Office 2010+, this should be 15.0, I believe).

Also, this code is UNTESTED, so please ensure you test it on a sample of the data and back up your file before running.

Hope this helps!

Public Sub CreateOutlookEmail_BillDavenport()
Dim OutApp          As Object, _
    OutMail         As Object
    
Dim strbody         As String

Dim i               As Long, _
    LR              As Long

'   NOTE: Must have references to the following libraries enabled:
'       * Microsoft Outlook 15.0 Object Library

LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    
    
    'Build string to enter into email body
    strbody = "Hello, the following order, " & Range("B" & i).Value & ", requires additional action by your department."
                
    
    On Error Resume Next
    With OutMail
        .To = Range("A" & i).Value & "@copper.fake"
        .CC = ""
        .BCC = ""
        .Subject = "Additional action requested - Order# " & Range("B" & i).Value
        .HTMLBody = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        '.Send
        .Display
    End With
    
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing
Next i

End Sub

that's working perfectly thank you, i'll mess around with it a bit but this is a great starting point. how can i modify this to populate a different email address for each tech type? this is currently just putting the tech as the address, i was just using that as an example i'm sorry for not being clear in my description, the distribution lists are actually different.

IE copper should go to orderresolution@fake.fake
copper bonded to bondedorderres@fake.fake
gpon to gponorderres@fake.fake

i'm sure i can mess around with it to figure out how to put that in your loop but you could probably save me some time. Thanks again man.

 

[MrKowz]

that's working perfectly thank you, i'll mess around with it a bit but this is a great starting point. how can i modify this to populate a different email address for each tech type? this is currently just putting the tech as the address, i was just using that as an example i'm sorry for not being clear in my description, the distribution lists are actually different.

IE copper should go to orderresolution@fake.fake
copper bonded to bondedorderres@fake.fake
gpon to gponorderres@fake.fake

i'm sure i can mess around with it to figure out how to put that in your loop but you could probably save me some time. Thanks again man.

Try:

Public Sub CreateOutlookEmail_BillDavenport()
Dim OutApp          As Object, _
    OutMail         As Object
    
Dim strbody         As String

[B][COLOR="#FF0000"]Dim distgrp         As String[/COLOR][/B]

Dim i               As Long, _
    LR              As Long

'   NOTE: Must have references to the following libraries enabled:
'       * Microsoft Outlook 15.0 Object Library

LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    
    
    'Build string to enter into email body
    strbody = "Hello, the following order, " & Range("B" & i).Value & ", requires additional action by your department."
         
[B][COLOR="#FF0000"]    Select Case Range("A" & i).Value
        Case "copper"
            distgrp = "orderresolution"
        Case "copper bonded"
            distgrp = "bondedorderres"
        Case "gpon"
            distgrp = "gponorderres"
    End Select[/COLOR][/B]
         
    
    On Error Resume Next
    With OutMail
        .To = [COLOR="#FF0000"][B]distgrp[/B][/COLOR] & "@copper.fake"
        .CC = ""
        .BCC = ""
        .Subject = "Additional action requested - Order# " & Range("B" & i).Value
        .HTMLBody = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        '.Send
        .Display
    End With
    
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing
Next i

End Sub

 

[Bill_Davenport]

working perfect, thanks a million.

 

[MrKowz]

working perfect, thanks a million.

Glad it works well for you. Thank you for the feedback, and have a great day!

 

[Bill_Davenport]

Glad it works well for you. Thank you for the feedback, and have a great day!

I'm circling back to this now as they've asked me to change the format of the subject line, currently the date in the sheet is formatted to look like this:

01/08/2017 8:00:00 AM I'd like it to just show 8AM (or just 8)
i applied a format to the date columns using the following code:

Selection.NumberFormat = "h"

however when i insert that column into the email it displays the value of the date cell instead of the hour. my subject looks like this:

*URGENT* North - Not ready 42948.3333333333 to
i want this
*URGENT* North - Not ready 8 AM

this is the code i'm using to generate the subject line of the email..

.Subject = "*URGENT* " & Range("H" & i).Value & " - Not ready " & Range("F" & i).Value & " to " & Range("G" & i).Value & ". Order# " & Range("B" & i).Value

any suggestions on how to get this to display the date properly? when i don't apply any formatting to the date it displays properly, as soon as i format the date to only show the hour i start having this problem.

 

[MrKowz]

The NumberFormat of a cell only affects what the cell displays, it doesn't affect the underlying value. We will need to define the format we want inside of the VBA.

Try:

.Subject = "*URGENT* " & Range("H" & i).Value & " - Not ready " & Format(Range("F" & i).Value, "h AM/PM") & " to " & Range("G" & i).Value & ". Order# " & Range("B" & i).Value

 

[Bill_Davenport]

The NumberFormat of a cell only affects what the cell displays, it doesn't affect the underlying value. We will need to define the format we want inside of the VBA.

Try:

.Subject = "*URGENT* " & Range("H" & i).Value & " - Not ready " & Format(Range("F" & i).Value, "h AM/PM") & " to " & Range("G" & i).Value & ". Order# " & Range("B" & i).Value

that worked and makes sense. So i was just applying the format too early? Thanks again!