I see a number of problems in your code, but I am not sure which one is keeping your emails from being created.
I cannot run your code without your file and some sample data. Feel free to attach a file if it does not contain private data.
Here are the things that need to be fixed:
What module is this code in? If you have it in a standard module (e.g., Module1) all range references will default to be whatever is the active sheet. You haven't given enough information for me to determine if that's what you want. What sheet do you mean to refer to
Dim strto, strcc, strsub, strbody As String
I am sure you think you have declared four String variables. However, you have declared three Variant variables and one String variable. You must specify a data type for each variable when you have multiple variables on the same Dim statement.
Dim strto As String, strcc As String, strsub As String, strbody As String
You have not declared BR.
You set sh
Set sh = Sheets("Master Orders")
but you never use it anywhere.
You are setting your greeting message "Good morning" etc., each time through the loop. You can do this one time before entering the loop, since it's going to be the same greeting for every email.
You are creating a loop, but referring only one cell. You don't need a loop to do this:
For Each Warehouse In Worksheets("Customer Data").Cells(i, 72)
If Warehouse = "Dartmouth" Then WH = Worksheets("Codes").Range("P1:V1").Value
If Warehouse = "Delta" Then WH = Worksheets("Codes").Range("P2:V2").Value
If Warehouse = "Winnipeg" Then WH = Worksheets("Codes").Range("P3:V3").Value
If Warehouse = "Mississauga" Then WH = Worksheets("Codes").Range("P4:V4").Value
If Warehouse = "Laval" Then WH = Worksheets("Codes").Range("P5:V5").Value
Next
Your If statements are mutually exclusive--you can only pick one. In such a case I would use a Case statement instead.
You are setting Warehouse, a String variable, to a range of values. This will not work the way you are expecting. It will only use the first value in the range. Are you trying to create a string that concatenates all the cells from column P to V?
I think you want something like this:
Dim R As Long
Dim Col as long
Select Case Worksheets("Customer Data").Cells(i, 72)
Case "Dartmouth"
R = 1
Case "Delta"
R = 2
Case "Winnipeg"
R = 3
Case "Mississauga"
R = 4
Case "Laval"
R = 5
End Select
For Col = 16 To 22
WH = WH & " " & Worksheets("Codes").Cells(R, Col).Value
Next Col
You don't need to .Display twice. It's not harmful, but the first one is not useful.
With OutMail
.Display
End With
signature = OutMail.HTMLbody
With OutMail
.To = strto
.CC = strcc
.Subject = strsub
.HTMLbody = strbody & vbNewLine & signature
'.send
.Display
End With
LinkBack URL
About LinkBacks
Jamesraywebber85 
Register To Reply
Bookmarks