Problem with Attachment in Macro

[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:


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

[Blakearino]

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

[westconn1]

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

[Nur]

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.. :ipray:


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..:dunno

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..:beg:

[westconn1]

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)

[Nur]

hi wesconn1 :hi:

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

[westconn1]

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