hi every one,
i'm new here.. i want to ask question about this macro thing.
So i have this macro which will sent email through outlook and currently it works well. but when i've tried to add the code for the attachment, it doesn't work. and it shows this error message:
"run time error: '424'
Object required"
and it was highlighted on this code: Attachments.Add ("C:\Users\NRosli2\Desktop\attachment\" & "Probation Appraisal Form" & ".doc")
this how the code looks like:
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
Dim r As Integer, x As Double
For r = 2 To 4 'data in rows 2-4[/COLOR]
' Get the email address from column 17 and 19
Email = Cells(r, 17) & ";" & Cells(r, 19)
' Message subject
Subj = "Staff confirmation for " & Cells(r, 2) & "_" & Cells(r, 8) 'get the employee's name and the segment
' Compose the message
Msg = ""
Msg = Msg & "Dear " & Cells(r, 16) & " and " & Cells(r, 18) & "," & vbCrLf & vbCrLf 'get manager's name from column 16 and 18
Msg = Msg & "Kindly be informed that your employee " & Cells(r, 2) & "'s " 'get employee's name from column 2
Msg = Msg & "probationary period has been expired on " & Cells(r, 10) & "." 'get the probation date from column 10
Msg = Msg & "Kindly take some time to run through with your employee on their performance during the probation period."
Msg = Msg & "Thanks and Regards," & vbCrLf[/COLOR]
' The attachments
Attachments.Add ("C:\Users\NRosli2\Desktop\attachment\" & "Probation Appraisal Form" & ".doc")
'Attachments.Add ("C:\Users\NRosli2\Desktop\attachment\Probation Matrix.pdf")
' 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"
Next r
End Sub
Please help me to solve the problem.
Thanks and Regards
nur