Email from Excel

[Edgar]

Hi

I use the following code to send emails from Excel. The
sheet that it uses has the following columns:

SuppID Name Email Curr Amount File Location
112585 AAA (e-mail address removed) AUD 3000 C:\remit\112585.msg
112652 BBB (e-mail address removed), NZD 5000 c:\remit\112652.msg

When i run the code it checks that there is an email
address in column C and a file location in column F and
then loops through the items sending an email with the
data in the list. I have used this before but i changed
the layout of the sheet to add some extra columns and
adjusted the offset values in the code but now when I run
the code it doesnt pick up the right file as an
attachment. Can anyone spot why this is happening?

Thanks in advance

Sub TestFile1()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim cell As Range
Application.ScreenUpdating = False
Set olApp = New Outlook.Application
For Each cell In Sheets("Sheet1").Columns
("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Offset(0, 1).Value <> "" Then
If cell.Value Like "*@*" And Dir(cell.Offset
(0, 1).Value) <> "" Then
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = cell.Value
.Subject = "Testfile"
.Body = "Hi " & cell.Offset(0, -
1).Value
.Attachments.Add cell.Offset(0,
1).Value
.Display 'Or use Display
End With
Set olMail = Nothing
End If
End If
Next cell
Set olApp = Nothing
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Hi Edgar

Look in the VBA help for the Offset function.
Try to understand it

Post back if you need more help

Sub TestFile1()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim cell As Range
Application.ScreenUpdating = False
Set olApp = New Outlook.Application
For Each cell In Sheets("Sheet1").Columns("C").Cells.SpecialCells(xlCellTypeConstants)
If cell.Offset(0, 3).Value <> "" Then
If cell.Value Like "*@*" And Dir(cell.Offset(0, 3).Value) <> "" Then
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = cell.Value
.Subject = "Testfile"
.Body = "Hi " & cell.Offset(0, -1).Value
.Attachments.Add cell.Offset(0, 3).Value
.Display 'Or use Display
End With
Set olMail = Nothing
End If
End If
Next cell
Set olApp = Nothing
Application.ScreenUpdating = True
End Sub

http://www.rondebruin.nl/sendmail.htm#file

 

[Michael Bednarek]

[To maximize your chances for meaninful answers, you should create a
new thread for a new subject.]

Cursory reading of your code suggest that Offset(0,1) can't be right
if you want to attach the file specified in column F.

Why do you hinge your "For Each cell"-loop on column B?