Hi everyone,

I require help maybe it's because I am blonde but I just can't seem to get my Marco running the way I need it to.

If you see below this is what I have wrote so far. I need the following to include the marco "SendEmail" to let me add a CC email address which would be Cells(ActiveCell.Row, 4) but everytime I try and press the end button it doesn’t come through just the "to" and the "subject" and the message of course.

I also need to enter another piece of data after the "Dear" the active cells to be from a different sheet on the same excel spredsheet called "Allocation" and its Row 3 I need but I don’t know how to pull it through only on the current Sheet 1.
I've wrote where the Allocation Row 3 needs to go below!

Sorry didnt know how to highlight it

If you could do it. I'd be very greatly as my brain is just mush right now.



Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long




Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Email = Cells(ActiveCell.Row, 9)

Subj = Cells(ActiveCell.Row, 5)

Msg = ""
Msg = Msg & "Dear " & Cells(Insert Allocation Row Three in here) & "," & vbCrLf & vbCrLf & "An entry has been made on your holiday sheet.. Please see details below details. " & vbCrLf & vbCrLf & "Reference: " & Cells(ActiveCell.Row, 1) & vbCrLf & vbCrLf & "Location: " & Cells(ActiveCell.Row, 2) & vbCrLf & vbCrLf & "Date of Holiday:" & Cells(ActiveCell.Row, 3) & vbCrLf & vbCrLf & "Return Date: " & Cells(ActiveCell.Row, 6) & vbCrLf & vbCrLf & "Please note this must be entered into the database by an allocation time of :" & Cells(ActiveCell.Row, 8)

'Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

'Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")

'Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

'Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

'Wait two seconds before sending keystrokes
'Application.Wait (Now + TimeValue("0:00:02"))
'Application.SendKeys "%s"
End Sub