Send Email From Excel

[LearnMeExcel]

Hi

i make code to send emails from Excel, in my PC it is working fine,
but in my Collegue it is not working, i get this msg
Active x compnent cant creat object

i activate the Outlook Refference fro tools -> Refrence

is there is any Prblem in his windows registry file or what ???

thankx in advanced

 

[AlphaFrog]

This thread may help...

http://www.mrexcel.com/forum/excel-...-cant-create-object-excel-outlook-2007-a.html

 

[LearnMeExcel]

sorry Alpha
the problem still as it
but is this problem from office or from windows

 

[Anthony47]

I know there are many gurus on this forum, but no any sorcerer... So I guess if you don't publish the code of the macro no one will be able to help...
Also, did you set a reference to the Outlook library on your computer or the one that does not work?

Bye

 

[LearnMeExcel]

the problem solved Alpha by your way but i get another error msg when the code try to Attached file from folder
this is my code

Sub EmailTOAll()
Dim FSO         As Object
Dim FLD         As Object
Dim wb          As Workbook
Dim FPath       As String
Dim OutApp      As Outlook.Application
Dim OutMail     As Object
Set wb = ActiveWorkbook
FPath = wb.Path
Set OutApp = New Outlook.Application
Set OutMail = OutApp.CreateItem(0)
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FLD = FSO.getfolder(FPath)

For Each file In FLD.Files
    If Not (WorksheetFunction.Substitute(file, FPath & "\", "") Like "*NewEva*") Then
        With OutMail
            .To = "me@Me.com"
            .Subject = "Test Mail "
            .body = "Hi"
            .Attachments.Add file
            .send
        End With
    End If
Next file
End Sub

i get this error msg
run-time error 438
Object doesn't support this property or method

 

[Anthony47]

Which line generate the error?
(the one that is highlighted when you press "debug")

Also, I don't recommend assigning a variable a reserved work; so replace anyway "file" (as a variable) with "myfile"

Bye

 

[LearnMeExcel]

Error on line
.attachement.add

You point about file / myfile not clear
Can explain more

 

[AlphaFrog]

The Outlook attachment requires the path and file name as a string (not the FSO object).

.Attachments.Add File.Path & "\" & File.Name

 

[Anthony47]

You point about file / myfile not clear
Can explain more

You defined a "variable" with the name "file"; but the word "file" in vba has its own meaning (check the help on line). So I recommend avoiding this potential confusion, and you replace the name "file" (in all its occourrences in the macro) with a different term, for example "myFile".
Let's go to the real problem.

FLD.Files returns a collection of files (again, see vba help on line: FileSystem Object, property Files); but "Attachments.Add" requires the address of the file, ie his full path & file name; this can be obtained using the property path of the item.
Thus:

            .Attachments.Add file.Path

I think the code from AlphaFrog (see above) is "oversized"

Bye

 

[AlphaFrog]

I think the code from AlphaFrog (see above) is "oversized"

.Attachments.Add file.Path

Thanks for the correction