Consulting

Results 1 to 7 of 7

Thread: Problem with Attachment in Macro

  1. #1
    VBAX Newbie
    Joined
    Jan 2014
    Posts
    4
    Location

    Smile Problem with Attachment in Macro

    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
    Last edited by Aussiebear; 01-12-2014 at 12:06 AM. Reason: Added code tags to submitted code

  2. #2
    Nur
    As I see that no one has responded I can help get you pointed in the general direction. (You may be more experienced than me). ("C:\Users\NRosli2\Desktop\attachment\" & "Probation Appraisal Form" & ".doc")" is a string not an object. For my purpose I wanted to browse to the file to select it. This required the use of .Application.FileDialog(msoFileDialogFilePicker)
    to get the object. See below for an idea. You will have to modify it to pick the file from the path you give it.
    Note: The reason that Excel is used is because Outlook cannot call FileDialog. You need to sneak Excel in the background to do that for you.

    (not my code just kluged from various forgotten sources)



    Public Sub Browseforfile(dar, footie)
        Dim otherObject As Excel.Application
        Dim fdFolder As Office.FileDialog
        Set otherObject = New Excel.Application
        otherObject.Visible = False
        Set fdFolder = otherObject.Application.FileDialog(msoFileDialogFilePicker)
        With fdFolder
            .InitialFileName = dar
            .InitialView = msoFileDialogViewDetails
            '3) Set the InitialView property to control how your files
            ' appear on screen (as a list, icons, etc.)
            .ButtonName = "Select"
            .AllowMultiSelect = False
            .Title = "Something Useful"
            .Show
    '4) To set the filters (you can have as many as you like)
    ' first clear any existing ones, then add them one by one
    ' .Filters.Clear
    ' .Filters.Add "Excel macros", "*.xlsm"
    ' if there's more than one filter, you can control which
    ' one is selected by default
    ' .FilterIndex = 1[/FONT]
    '    Debug.Print fdFolder.SelectedItems(1)
        End With
        footie = fdFolder.SelectedItems(1)
        otherObject.Quit
        Set otherObject = Nothing
    End Sub
    Last edited by Blakearino; 02-13-2014 at 04:39 PM. Reason: posting sc##wed the whole thing up

  3. #3
    ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
    this is not automating outlook, but using mailto which will open a message from whatever is the default email client on the system
    there is no attachments.add method for this, the attachment has to be part of the string

    maybe like
    att = "C:\Users\NRosli2\Desktop\attachment\" & "Probation Appraisal Form" & ".doc"
    URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg & "&att:filename=" & att
    which is pretty much what i posted in your other thread
    if you want to have full control over the email, you should look at automating outlook, or using CDO instead

  4. #4
    VBAX Newbie
    Joined
    Jan 2014
    Posts
    4
    Location

    Post Problem : Compile Error

    Hi everyone,

    Sorry for posting this new problem to this same thread.. I've tried but i couldn't find the button to create a new post..i'm so sorry..


    so.. here's the problem.. I've working on this code where it will update data from current sheet to the next sheet.. and the data that going to be updated must meet either one of these condition (confirm, pending & not now).

    the problem that i've facing is, there's an error in my code which i'm totaly have no idea what does it means..

    the error is " Compile Error: Invalid qualifier"

    and the xlUp(bold) in this line of code been highlighted when the error message showed up.

    For i = 2 To ws1.Range("A20").End(xlUp).Row
    If ws1.Cells(i, 11) = "pending" Then ws1.Rows(i).Copy ws2.Rows(ws2.Cell(ws2.Rows, Count, 11).End(xlUp.Row + 1))
    Next i
    and the line of code came from this function..

    Sub UpdateDataJanuary()
    Dim i As Integer, j As Integer, k As Integer
    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("January")
    Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("February")
    For i = 2 To ws1.Range("A20").End(xlUp).Row
    If ws1.Cells(i, 11) = "pending" Then ws1.Rows(i).Copy ws2.Rows(ws2.Cell(ws2.Rows, Count, 11).End(xlUp.Row + 1))
    Next i
    For j = 2 To ws1.Range("A20").End(xlUp).Row
    If ws1.Cells(j, 11) = "extend" Then ws1.Rows(j).Copy ws2.Rows(ws2.Cells(ws2.Rows, Count, 11).End(xlUp.Row + 1))
    Next j
    For k = 2 To ws1.Range("A20").End(xlUp).Row
    If ws1.Cells(k, 11) = "Not Confirm" Then ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows, Count, 11).End(xlUp.Row + 1))
    Next k
    End Sub

    can any of you help me to solve this problem? pleasee..
    Last edited by Bob Phillips; 03-12-2014 at 02:55 AM. Reason: Added VBA tags

  5. #5
    try change to
    If ws1.Cells(i, 11) = "pending" Then ws1.Rows(i).Copy ws2.Rows(ws2.Cell(ws2.Rows, Count, 11).End(xlUp).Row + 1)

  6. #6
    VBAX Newbie
    Joined
    Jan 2014
    Posts
    4
    Location
    hi wesconn1

    thanks for your help..i've tried replacing my code with your code.. but unfortunately, if doesn't work ..
    and still there is an error, but this time it is syntax error..

  7. #7
    if you just pasted in, make sure you have removed the bold formatting tags from the forum post, as i am sure VB will not like them

    If ws1.cells(i, 11) = "pending" Then ws1.rows(i).Copy ws2.rows(ws2.Cells(ws2.rows.Count, 11).End(xlUp).Row + 1)
    i did not see them when i copied the code from your post
    also fix some other error when editing

Posting Permissions

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