Calling sub

llan_man

New Member
Joined
Nov 6, 2005
Messages
45
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.

Code:
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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top