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]

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

It's more of a matter that the Range().Value method returns the value of the cell. No matter what the number format of the cell is in the worksheet, the value is still the same. All dates/time are represented as a whole number of days from 1/0/1900 and a decimal number of time. For example, the displayed date 8/1/2017 is actually the value 42948. The time 8:00 AM is actually the value 0.33333. 8/1/2017 8:00 AM is 42948.33333.

So all we need to do is when we pull the value into VBA, we have to explicitly tell VBA how we want it formatted.

Hope that helps, and thanks for the feedback! Glad it's working for you.

 

[Bill_Davenport]

oh ok, thanks for explaining it. makes sense to me

 

[Bill_Davenport]

so i've done some more work on this script and it's doing exactly what I want, minus one thing. i'm curious how can i add to this while it's looping through the rows i'd like it to check a cell in the row and if the number in that cell is 6 digits long i want it to set the body and subject to be something different. I've tried using different Len functions but i have not been able to get it to work properly for me. How can I add that to this script? Here's my modified version of the script if you want to work off of that. I've deleted the email addresses out of the script obviously I also put comments on where i think the code should go checking column I if it's length is 6 or less, but i havn't been able to get anything I have tried to work. I welcome any suggestions.

Public Sub CreateOutlookEmail()
Dim OutApp          As Object, _
    OutMail         As Object
    
Dim strbody         As String
Dim cclist          As String
Dim distgrp         As String
Dim province        As String
Dim strsubject      As String


Dim i               As Long, _
    LR              As Long


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




'generate correct CC list
Dim strMsg, inp01, strTitle, strFlag


strTitle = "Which region are you emailing from?"


strMsg = "Enter 1 for AB south" & vbCr
strMsg = strMsg & "Enter 2 for AB north" & vbCr
strMsg = strMsg & "Enter 3 for Interior" & vbCr
strMsg = strMsg & "Enter 4 for LML" & vbCr




strFlag = False


Do While strFlag = False


inp01 = InputBox(strMsg, "Choose your region")


Select Case inp01
    Case "1"
        cclist = ""
        province = ""
        strFlag = True
    Case "2"
        cclist = ""
        province = ""
        strFlag = True
    Case "3"
        cclist = ""
        province = ""
        strFlag = True
    Case "4"
        cclist = ""
        province = ""
        strFlag = True
    Case Else
        MsgBox "You made an incorrect selection!", 64, strTitle
End Select
Loop




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
    strsubject = "*URGENT* " & format(Range("F" & i).Value, "hhmm") & " to " & format(Range("G" & i).Value, "hhmm") & " " & Range("H" & i).Value & " - Not ready " & ". Order# " & Range("B" & i).Value
    strbody = "Hello, the following " & Range("A" & i).Value & " order in " & province & " " & Range("B" & i).Value & ", requires additional action. Please review at your earliest convenience. Customer name is " & Range("E" & i).Value & " and their appointment window is between " & Range("F" & i).Value & " and " & Range("G" & i).Value
    
    'generate the correct "To" list
    Select Case Range("A" & i).Value
        Case "Copper"
            distgrp = ""
        Case "Copper Bonded"
            distgrp = ""
        Case "Satellite"
            distgrp = ""
        Case "GPON"
            distgrp = ""
    End Select
    'identify wholesale orders and change body & subject
    Select Case Range("C" & i).Value
        Case "I"
            distgrp = ""
            strsubject = "Not Ready for Dispatch - Business"
            strbody = "BTN: " & Range("I" & i).Value
            strbody = strbody & ", call ID: " & Range("J" & i).Value
            strbody = strbody & " is not ready for dispatch and requires action by your department."
        Case "T"
            distgrp = ""
            strsubject = "Not Ready for Dispatch - Business"
            strbody = "BTN: " & Range("I" & i).Value
            strbody = strbody & ", call ID: " & Range("J" & i).Value
            strbody = strbody & " is not ready for dispatch and requires action by your department."
        Case "C"
            distgrp = ""
            strsubject = "Not Ready for Dispatch - Business"
            strbody = "BTN: " & Range("I" & i).Value
            strbody = strbody & ", call ID: " & Range("J" & i).Value
            strbody = strbody & " is not ready for dispatch and requires action by your department."
    End Select


'check for town orders
'this is where i'd like to include the check for 6 digit long in column I


   
    On Error Resume Next
    With OutMail
        .To = distgrp
        .CC = cclist
        .BCC = ""
        .Subject = strsubject
        .HTMLBody = strbody
        .Display
    End With
    
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing
Next i


End Sub

 

[MrKowz]

We should be able to accomplish this with the LIKE statement (Like Operator (Visual Basic) | Microsoft Docs). This will allow us to actually check for 6 numerical characters.

Try playing around with this block of code:

    If Range("I" & i).Value Like "######" Then
        'What you want it to do if Col I has 6 digits
    Else
        'What you want it to do if Col I does not have 6 digits
    End If

 

[Bill_Davenport]

We should be able to accomplish this with the LIKE statement (Like Operator (Visual Basic) | Microsoft Docs). This will allow us to actually check for 6 numerical characters.

Try playing around with this block of code:

    If Range("I" & i).Value Like "######" Then
        'What you want it to do if Col I has 6 digits
    Else
        'What you want it to do if Col I does not have 6 digits
    End If

works perfect, thanks again. I had tried an if statement but i was trying to do a variation of if len(cell) <7 then and it wasn't working. i was close thanks man

 

[MrKowz]

Happy to help! The Like statement is pretty darn powerful for string pattern matching (not quite as powerful as full blown RegEx though).

Have a good weekend!