Calling sub

[llan_man]

Hi can anyone tell me what I have done wrong here.

The following code calls a procedure called Update_EmailLog

Dim Apprej_Sender, Apprej_SenderMail, Apprej_Receiver, Apprej_ReceiverMail, Apprej_Subject, Appej_BodyMail As String

Apprej_Sender = Sheets("ADMIN").Range("c9")
Apprej_SenderMail = Sheets("ADMIN").Range("c4")
Apprej_Receiver = Sheets("ADMIN").Range("c7")
Apprej_ReceiverMail = Sheets("ADMIN").Range("c3")
Apprej_Subject = "The Budget for " & Sheets("ADMIN").Range("c6") & " has been " & apprej_decision & " by " & Sheets("ADMIN").Range("c9")
Apprej_BodyMail = Apprej_Subject & vbCr & vbCr & _
"The following comments have been made: " & vbCr & _
apprej_comment & vbCr & vbCr & _
"The following areas have been identified as requiring further work : " & vbCr & _
"Please contact " & Apprej_Sender & " if you require further information."

Call Update_EmailLog(Apprej_Sender, Apprej_SenderMail, Apprej_Receiver, Apprej_ReceiverMail, Apprej_Subject, Apprej_BodyMail)



The Updat_EmailLog is as follows:

Public Sub Update_EmailLog(Sender, SenderMail, Receiver, ReceiverMail, Subject, BodyMail As String)

Dim LastRowMail As Long
ActiveSheet.UsedRange
LastRowMail = Cells.SpecialCells(xlLastCell).Rowl
NextRowMail = LastRowMail + 1

Range("A" & NextRowMail) = Date
Range("B" & NextRowMail) = Sender
Range("C" & NextRowMail) = SenderMail
Range("D" & NextRowMail) = Receiver
Range("E" & NextRowMail) = ReceiverMail
Range("F" & NextRowMail) = Subject
Range("G" & NextRowMail) = BodyMail
Range("A" & NextRowMail & ":G" & NextRowMail).Select
Selection.WrapText = True
Selection.Interior.ColorIndex = 0
Selection.VerticalAlignment = xlVAlignCenter
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
Selection.Borders(xlInsideVertical).LineStyle = xlContinuous

End Sub


When I run the first process, I get the following error with the Apprej_BodyMail highlighted in the call statement.

Compile Error:
ByRef arguement type mismatch.

All help gratefully received.

Regards

Harv

 

[Scott Huish]

I hope you don't mind but I cleaned this up a bit, I also changed how you find the last row.

Basically, you have some spelling errors. I also assumed you wanted all your variables dimmed as strings, so I fixed that too.

Sub test()
Dim Apprej_Sender As String, Apprej_SenderMail As String
Dim Apprej_Receiver As String, Apprej_ReceiverMail As String
Dim Apprej_Subject As String, Apprej_BodyMail As String

With Sheets("ADMIN")
    Apprej_Sender = .Range("C9")
    Apprej_SenderMail = .Range("C4")
    Apprej_Receiver = .Range("C7")
    Apprej_ReceiverMail = .Range("C3")
    Apprej_Subject = "The Budget for " & .Range("C6") & " has been " & apprej_decision & " by " & .Range("C9")
    Apprej_BodyMail = Apprej_Subject & vbLf & vbLf & _
        "The following comments have been made: " & vbLf & _
        apprej_comment & vbLf & vbLf & _
        "The following areas have been identified as requiring further work : " & vbLf & _
        "Please contact " & Apprej_Sender & " if you require further information."
End With
Call Update_EmailLog(Apprej_Sender, Apprej_SenderMail, Apprej_Receiver, Apprej_ReceiverMail, _
    Apprej_Subject, Apprej_BodyMail)
End Sub

Public Sub Update_EmailLog(Sender As String, SenderMail As String, Receiver As String, _
    ReceiverMail As String, Subject As String, BodyMail As String)

Dim NextRowMail As Long

NextRowMail = Range("A65536").End(xlUp).Row + 1

Range("A" & NextRowMail) = Date
Range("B" & NextRowMail) = Sender
Range("C" & NextRowMail) = SenderMail
Range("D" & NextRowMail) = Receiver
Range("E" & NextRowMail) = ReceiverMail
Range("F" & NextRowMail) = Subject
Range("G" & NextRowMail) = BodyMail

With Range("A" & NextRowMail & ":G" & NextRowMail)
    .WrapText = True
    .Interior.ColorIndex = 0
    .VerticalAlignment = xlVAlignCenter
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlInsideVertical).LineStyle = xlContinuous
End With

End Sub

EDIT: fixed how your lines were breaking

 

[llan_man]

Thanks. That worked a treat and I appreciate your advice on cleaning the code up.

Cheers

Harv