hi there,
i tried several codes to send some mails from excel via outlook, with the addys in the excel cells, but it didnt work out. the code below must be right (i tested also a lot of other skrips) sinde it works for example when deaktivate the .send and activate the .display...
but when i try to send something, excel asks me, if i really want to send (with every mail - for example, when i send 20 mails, it asks 20 times)
and after clicking on "yes my lovely excel, you can send) it doesnt send anythink - my email account is empty???
below you can see the codce -
thank you very much for any kind of advice
cheers
Stefan
here the code:
Sub TestFile()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
On Error GoTo cleanup
For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder"
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
"Please contact us to discuss bringing your account up to date"
'You can add files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'Or use Display
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
oh besides, i had to cathegorize myself as a programmer, but i wouldnt really call myself a programmer - that sounds too advanced.
i tried several codes to send some mails from excel via outlook, with the addys in the excel cells, but it didnt work out. the code below must be right (i tested also a lot of other skrips) sinde it works for example when deaktivate the .send and activate the .display...
but when i try to send something, excel asks me, if i really want to send (with every mail - for example, when i send 20 mails, it asks 20 times)
and after clicking on "yes my lovely excel, you can send) it doesnt send anythink - my email account is empty???
below you can see the codce -
thank you very much for any kind of advice
cheers
Stefan
here the code:
Sub TestFile()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
On Error GoTo cleanup
For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder"
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
"Please contact us to discuss bringing your account up to date"
'You can add files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'Or use Display
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
oh besides, i had to cathegorize myself as a programmer, but i wouldnt really call myself a programmer - that sounds too advanced.
