Attach file to email

[Nur]

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:

[vba]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
' 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


' 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[/vba]


Please help me to solve the problem.

Thanks and Regards
nur

[westconn1]

see post #10 above
use code tags when posting code

you are calling shellexecute "mailto" to send email, totally different to automating outlook

copied from a previous post of mine elsewhere

try
"&att:filename=" & "E:\ikvinder work\test1.xls"

you may need to test for the default client to know the correct syntax

i am reasonably sure i tested this at the time, change the filepath\name to suit, just add after body, this was the format for outlook express, there may be differences for other clients

i have never tested with multiple attachment

[Zack Barresse]

I formatted your code using the VBA tags. Please use that when posting code.

Also, I moved your question to it's own thread, as previously posted here: http://www.vbaexpress.com/forum/showthread.php?t=42863
Please don't hijack someone else's thread. Feel free to link to it, as i just did.

[westconn1]

I formatted your code using the VBA tags. Please use that when posting code.

Also, I moved your question to it's own thread, as previously posted here: http://www.vbaexpress.com/forum/showthread.php?t=42863
Please don't hijack someone else's thread. Feel free to link to it, as i just did.

hard for anyone new to post links

[Zack Barresse]

Not really. Navigate to the thread you want, select the URL in the address bar of your browser, press CTRL + C to copy it, when typing your thread press CTRL + V to paste it, voila.

[westconn1]

i tried that early on and got a message to say i could not post a url, as i did not have enough posts
maybe that does not apply to urls from within this forum?

[Zack Barresse]

Sorry I didn't see your response immediately. I had to go back and check all the php settings, and indeed it's a minimum post count of 10 before posting URL's (for spamming issues). Sorry for the oversight!

@Nur: haven't heard back from you. Still there?