Consulting

Results 1 to 7 of 7

Thread: Attach file to email

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Newbie
    Joined
    Jan 2014
    Posts
    4
    Location

    Attach file to email

    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
    Last edited by Zack Barresse; 01-19-2014 at 12:55 PM. Reason: Added VBA tags

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •