A
Abay
Hello ... At a new job I am using Excel 2007 for the first time. I have
used 2003 at home at at previous jobs.
I have a macro which takes email addresses, file names and locations from
cells in Excell and generates emails plus file attachements using
Outlook/Outlook Express. This works fine in the 2003 version but not in
2007.
I got the macro from an Excel usergroup like this one back in 2007 ..
hopefully this explains that I am no expert in Excel, which also explains
why I am at a loss now.
I get the error message: Run time error 429, "ActiveX component can't create
object". at the line
" Set OutApp = CreateObject("Outlook.Application")"
I am very new to the 2007 version and am still finding my way around it's
different appearance.
Any help with this would be most appreciated.
Abay
Following please find the macro code:
Sub email()
'
' email Macro
' Macro recorded 2/19/2007 by MTK
'
' Keyboard Shortcut: Ctrl+m
'
'Working in 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range, FileCell As Range, rng As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set sh = Sheets("Sheet1")
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
'Enter the file names in the C:Z column in each row
Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")
If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = cell.Value
.Subject = "Attached please find your current statement"
.Body = " " & cell.Offset(0, -1).Value
For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell) <> "" Then
If Dir(FileCell.Value) <> "" Then
.Attachments.Add FileCell.Value
End If
End If
Next FileCell
.Send 'Or use Display
End With
Set OutMail = Nothing
End If
Next cell
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
used 2003 at home at at previous jobs.
I have a macro which takes email addresses, file names and locations from
cells in Excell and generates emails plus file attachements using
Outlook/Outlook Express. This works fine in the 2003 version but not in
2007.
I got the macro from an Excel usergroup like this one back in 2007 ..
hopefully this explains that I am no expert in Excel, which also explains
why I am at a loss now.
I get the error message: Run time error 429, "ActiveX component can't create
object". at the line
" Set OutApp = CreateObject("Outlook.Application")"
I am very new to the 2007 version and am still finding my way around it's
different appearance.
Any help with this would be most appreciated.
Abay
Following please find the macro code:
Sub email()
'
' email Macro
' Macro recorded 2/19/2007 by MTK
'
' Keyboard Shortcut: Ctrl+m
'
'Working in 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range, FileCell As Range, rng As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set sh = Sheets("Sheet1")
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
'Enter the file names in the C:Z column in each row
Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")
If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = cell.Value
.Subject = "Attached please find your current statement"
.Body = " " & cell.Offset(0, -1).Value
For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell) <> "" Then
If Dir(FileCell.Value) <> "" Then
.Attachments.Add FileCell.Value
End If
End If
Next FileCell
.Send 'Or use Display
End With
Set OutMail = Nothing
End If
Next cell
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub