PDA

View Full Version : [SOLVED] Mailbatch with 2 different bodies based on cell



Fean
04-13-2016, 02:37 AM
Hi,

Can somebody help me why this debugs?

Why cant i make an "if then else statement" on the mailbody? (strbody else strbody2)





StrBody = "" & _
"Please sort aout this body1 blablabla body1." & vbNewLine & _
"" & vbNewLine & _
"" & vbNewLine & _
"Kind regards,"

StrBody2 = "" & _
"Please not that blbalablablablablalbalb Body2." & vbNewLine & _
"" & vbNewLine & _
"Kind regards,"

strAttachPath = ActiveSheet.Range("Path")
Dispname = ActiveSheet.Range("USERID").Text
Dim varAttachments As Variant
Dim i As Variant
Dim iRow As Integer
iRow = 7

Do While Cells(iRow, 1) <> ""
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.Recipients.Add Trim(Cells(iRow, 1))
If Cells(iRow, 2) <> "" Then .Recipients.Add Trim(Cells(iRow, 2))
If Len(StrSubject) > 0 Then .Subject = ActiveSheet.Cells(iRow, 8) & StrSubject

'---------------------------------
'---------------------------------
'it should go.. Irow 9 contains a 0 then strbody else strbody2
If Cells(iRow, 9) = 0 Then
.body = "Dear " & ActiveSheet.Cells(iRow, 4) & vbNewLine & vbNewLine & StrBody & vbNewLine & Dispname
Else
' Here it debugs?
.body = "Dear " & ActiveSheet.Cells(iRow, 4) & vbNewLine & vbNewLine & StrBody2 & vbNewLine & Dispname
End If
'----------------------------------

' If Len(StrBody) > 0 Then .body = "Dear " & ActiveSheet.Cells(iRow, 4) & vbNewLine & vbNewLine & StrBody & vbNewLine & Dispname
If Cells(iRow, 3) > 0 Then

' check if file exists
Dim strFullPath As String
strFullPath = strAttachPath & "\" & Trim(Cells(iRow, 3))
varAttachments = Split(Cells(iRow, 3).Text, ";")
If IsArray(varAttachments) Then
For i = LBound(varAttachments) To UBound(varAttachments)
strFullPath = strAttachPath & "\" & varAttachments(i)
If Dir(strFullPath, vbDirectory) = vbNullString Then
MsgBox ("Warning, the file" & Chr(10) & strFullPath & Chr(10) & "does not exist"), vbCritical
Cells(iRow, 5) = "Attachement missing!"
Else
.Attachments.Add strFullPath
Cells(iRow, 6) = "Processed"
End If
Next i
Else
If Dir(strFullPath, vbDirectory) = vbNullString Then
MsgBox ("Warning, the file" & Chr(10) & strFullPath & Chr(10) & "does not exist")
Cells(iRow, 5) = "Attachement missing!"
Else
strFullPath = strAttachPath & "\" & varAttachments
.Attachments.Add strFullPath
End If
End If
End If
.display '.Send its sends it..
Cells(iRow, 6) = "Processed.."
DoEvents

End With
iRow = iRow + 1
Loop
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

snb
04-13-2016, 03:47 AM
crossposted:

http://www.excelguru.ca/forums/showthread.php?5835-Mailbatch-with-2-bodies-based-on-cell

Fean
04-13-2016, 07:49 AM
Solved it myself.
Pretty stupid... The second person (email) wasnt present:doh::doh:




If ActiveSheet.Cells(iRow, 9) = "1" Then
StrBody = "" & _
"NOT PASS " & vbNewLine & _
"" & vbNewLine & _
"Kind regards,"
Else
StrBody = "" & _
"PASS " & vbNewLine & _
"" & vbNewLine & _
"Kind regards,"
End If
With OutMail
.Recipients.Add Trim(Cells(iRow, 1))
If Cells(iRow, 2) <> "" Then .Recipients.Add Trim(Cells(iRow, 2))
If Len(StrSubject) > 0 Then .Subject = ActiveSheet.Cells(iRow, 8) & StrSubject

'If Len(StrBody) > 0 Then .body = "Dear " & ActiveSheet.Cells(iRow, 4) & vbNewLine & vbNewLine & StrBody & vbNewLine & Dispname
.body = "Dear " & ActiveSheet.Cells(iRow, 4) & vbNewLine & vbNewLine & StrBody & vbNewLine & Dispname

arthurbr
04-13-2016, 08:58 AM
OP cross posts regularly (see excelguru.ca)