ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Excel emailing from spreadsheet (https://www.excelbanter.com/excel-programming/375449-excel-emailing-spreadsheet.html)

[email protected]

Excel emailing from spreadsheet
 
I was wondering if there is a way I can include, in the subject field,
a reference number from the spreadsheet I am emailing. I am hoping to
acheive this in a similar way that the email address is sourced. Below
is a copy of the VB code I am currently using.

Dim WBname As String
' Dim Flds As Variant
Application.ScreenUpdating = False
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
With Flds

..Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

..Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
"mail.xxxx.com.au"

..Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport")
= 25

..Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")
= 1

..Item("http://schemas.microsoft.com/cdo/configuration/sendusername") =
"xxxx"

..Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") =
"xxxx"
.Update
End With
For Each ws In ThisWorkbook.Worksheets
If ws.Range("a1").Value Like "?*@?*.?*" Then
ws.Copy
Set wb = ActiveWorkbook
WBname = "c:/ NCF" & ws.Name & ".xls"
wb.SaveAs WBname
wb.Close False
Set wb = Nothing
Set iMsg = CreateObject("CDO.Message")
With iMsg
Set .Configuration = iConf
.To = ws.Range("a1").Value
.Bcc = "
.From = """xxx"" "
.Subject = "Loan Revaluation Drawdown Advice: "
.AddAttachment WBname
.TextBody = "Please see attached"
.Send
End With
Set iMsg = Nothing
Kill WBname
End If
Next ws
Set iConf = Nothing
Application.ScreenUpdating = True
Sheets("Register").Select

Thanks

Russell


Franz Verga

Excel emailing from spreadsheet
 
wrote:
I was wondering if there is a way I can include, in the subject field,
a reference number from the spreadsheet I am emailing. I am hoping to
acheive this in a similar way that the email address is sourced. Below
is a copy of the VB code I am currently using.

[cut]

Hi Russell,

try he

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

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy




All times are GMT +1. The time now is 12:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com