Consulting

Results 1 to 7 of 7

Thread: Attach file to email

  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

  2. #2
    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

  3. #3
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,897
    Location
    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.

  4. #4
    Quote Originally Posted by Zack Barresse View Post
    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

  5. #5
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,897
    Location
    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.

  6. #6
    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?

  7. #7
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,897
    Location
    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?

Posting Permissions

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