Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot send mail in Office
I already test my marco at home, it work. But in Office, cannot send
out and shown error message "Run-time error '-2147220960 (80040220)' The "SendUsing" configuration value is invalid." Office is use "MS exchange", don't know how do set the code for loggin user name/password (because i have 2 email account). below is sample it work at home "SMTP" Sub Send() myMsg = "Send out email Now?" myTitle = "Send out" myBtn = MsgBox(myMsg, vbOKCancel + vbExclamation, myTitle) If myBtn = 1 Then 'Working in 2000-2007 Dim iMsg As Object Dim iConf As Object Dim sh As Worksheet Dim cell As Range, FileCell As Range, rng As Range ' Dim Flds As Variant With Application .EnableEvents = False .ScreenUpdating = False End With Set sh = Sheets("Lookup") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConst ants) '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) If Val(Application.Version) = 12 Then If wb.FileFormat = 51 And wb.HasVBProject = True Then MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _ "Save the file first as xlsm and then try the macro again.", vbInformation Exit Sub End If End If With Application .ScreenUpdating = False .EnableEvents = False End With Set iMsg = CreateObject("CDO.Message") 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") = "smtpo.hkbn.net" ' .Item("http://schemas.microsoft.com/cdo/configuration/ smtpserverport") = 25 ' .Update ' End With With iMsg Set .Configuration = iConf .To = cell.Value .BCC = "" .Subject = cell.Offset(0, -1).Value & " SmarTone-Vodafone Bill" & " - " & Format(Now, "mmmm yy") .TextBody = "Dear Customer," & vbNewLine & vbNewLine & _ "Please contact us on or before " & Format(Now, "mmmm") For Each FileCell In rng.SpecialCells(xlCellTypeConstants) If Trim(FileCell) < "" Then If Dir(FileCell.Value) < "" Then .AddAttachment 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 If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot send mail in Office
Random question (with reason)
Are you opening the Excel file from a link on an intranet page at work? I ask because I have a similar issue where the 'email' part of our excel files falls over *if* I open them from hyperlinks on the intranet - if I open the file from the network via windows explorer the macros work fine. I tried a few different methods of emailing from VBA but they all had an error somewhere in the code if opened via the intranet. The work around is to get Excel files to open in Excel. Microsoft explain he- http://support.microsoft.com/?scid=162059 So I am currently trying to get our systems guys to apply one of those 'Fixes' globally to all the PCs. " wrote: I already test my marco at home, it work. But in Office, cannot send out and shown error message "Run-time error '-2147220960 (80040220)' The "SendUsing" configuration value is invalid." Office is use "MS exchange", don't know how do set the code for loggin user name/password (because i have 2 email account). below is sample it work at home "SMTP" Sub Send() myMsg = "Send out email Now?" myTitle = "Send out" myBtn = MsgBox(myMsg, vbOKCancel + vbExclamation, myTitle) If myBtn = 1 Then 'Working in 2000-2007 Dim iMsg As Object Dim iConf As Object Dim sh As Worksheet Dim cell As Range, FileCell As Range, rng As Range ' Dim Flds As Variant With Application .EnableEvents = False .ScreenUpdating = False End With Set sh = Sheets("Lookup") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConst ants) '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) If Val(Application.Version) = 12 Then If wb.FileFormat = 51 And wb.HasVBProject = True Then MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _ "Save the file first as xlsm and then try the macro again.", vbInformation Exit Sub End If End If With Application .ScreenUpdating = False .EnableEvents = False End With Set iMsg = CreateObject("CDO.Message") 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") = "smtpo.hkbn.net" ' .Item("http://schemas.microsoft.com/cdo/configuration/ smtpserverport") = 25 ' .Update ' End With With iMsg Set .Configuration = iConf .To = cell.Value .BCC = "" .Subject = cell.Offset(0, -1).Value & " SmarTone-Vodafone Bill" & " - " & Format(Now, "mmmm yy") .TextBody = "Dear Customer," & vbNewLine & vbNewLine & _ "Please contact us on or before " & Format(Now, "mmmm") For Each FileCell In rng.SpecialCells(xlCellTypeConstants) If Trim(FileCell) < "" Then If Dir(FileCell.Value) < "" Then .AddAttachment 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 If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot send mail in Office
On Oct 8, 11:47 pm, Damon wrote:
Random question (with reason) Are you opening the Excel file from a link on an intranet page at work? I ask because I have a similar issue where the 'email' part of our excel files falls over *if* I open them from hyperlinks on the intranet - if I open the file from the network via windows explorer the macros work fine. I tried a few different methods of emailing from VBA but they all had an error somewhere in the code if opened via the intranet. The work around is to get Excel files to open in Excel. Microsoft explain he- http://support.microsoft.com/?scid=162059 So I am currently trying to get our systems guys to apply one of those 'Fixes' globally to all the PCs. " wrote: I already test my marco at home, it work. But in Office, cannot send out and shown error message "Run-time error '-2147220960 (80040220)' The "SendUsing" configuration value is invalid." Office is use "MS exchange", don't know how do set the code for loggin user name/password (because i have 2 email account). below is sample it work at home "SMTP" Sub Send() myMsg = "Send out email Now?" myTitle = "Send out" myBtn = MsgBox(myMsg, vbOKCancel + vbExclamation, myTitle) If myBtn = 1 Then 'Working in 2000-2007 Dim iMsg As Object Dim iConf As Object Dim sh As Worksheet Dim cell As Range, FileCell As Range, rng As Range ' Dim Flds As Variant With Application .EnableEvents = False .ScreenUpdating = False End With Set sh = Sheets("Lookup") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConst ants) '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) If Val(Application.Version) = 12 Then If wb.FileFormat = 51 And wb.HasVBProject = True Then MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _ "Save the file first as xlsm and then try the macro again.", vbInformation Exit Sub End If End If With Application .ScreenUpdating = False .EnableEvents = False End With Set iMsg = CreateObject("CDO.Message") 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") = "smtpo.hkbn.net" ' .Item("http://schemas.microsoft.com/cdo/configuration/ smtpserverport") = 25 ' .Update ' End With With iMsg Set .Configuration = iConf .To = cell.Value .BCC = "" .Subject = cell.Offset(0, -1).Value & " Subject linel" & " - " & Format(Now, "mmmm yy") .TextBody = "Dear Customer," & vbNewLine & vbNewLine & _ "Please contact us on or before " & Format(Now, "mmmm") For Each FileCell In rng.SpecialCells(xlCellTypeConstants) If Trim(FileCell) < "" Then If Dir(FileCell.Value) < "" Then .AddAttachment 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 If End Sub- Hide quoted text - - Show quoted text - No, I open the file use Excel in my working place's computer (Office), it cannot work, but at my Home's computer, it can work. It is because my company using "MS Exchange" so I cannot use my Macro? Did you know how can set the code about this, I have 2 MS Exchange email account, how can I login with fix user name? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot send mail in Office
" wrote:
On Oct 8, 11:47 pm, Damon wrote: Random question (with reason) Are you opening the Excel file from a link on an intranet page at work? I ask because I have a similar issue where the 'email' part of our excel files falls over *if* I open them from hyperlinks on the intranet - if I open the file from the network via windows explorer the macros work fine. I tried a few different methods of emailing from VBA but they all had an error somewhere in the code if opened via the intranet. The work around is to get Excel files to open in Excel. Microsoft explain he- http://support.microsoft.com/?scid=162059 So I am currently trying to get our systems guys to apply one of those 'Fixes' globally to all the PCs. " wrote: I already test my marco at home, it work. But in Office, cannot send out and shown error message "Run-time error '-2147220960 (80040220)' The "SendUsing" configuration value is invalid." Office is use "MS exchange", don't know how do set the code for loggin user name/password (because i have 2 email account). below is sample it work at home "SMTP" Sub Send() myMsg = "Send out email Now?" myTitle = "Send out" myBtn = MsgBox(myMsg, vbOKCancel + vbExclamation, myTitle) If myBtn = 1 Then 'Working in 2000-2007 Dim iMsg As Object Dim iConf As Object Dim sh As Worksheet Dim cell As Range, FileCell As Range, rng As Range ' Dim Flds As Variant With Application .EnableEvents = False .ScreenUpdating = False End With Set sh = Sheets("Lookup") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConst ants) '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) If Val(Application.Version) = 12 Then If wb.FileFormat = 51 And wb.HasVBProject = True Then MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _ "Save the file first as xlsm and then try the macro again.", vbInformation Exit Sub End If End If With Application .ScreenUpdating = False .EnableEvents = False End With Set iMsg = CreateObject("CDO.Message") 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") = "smtpo.hkbn.net" ' .Item("http://schemas.microsoft.com/cdo/configuration/ smtpserverport") = 25 ' .Update ' End With With iMsg Set .Configuration = iConf .To = cell.Value .BCC = "" .Subject = cell.Offset(0, -1).Value & " Subject linel" & " - " & Format(Now, "mmmm yy") .TextBody = "Dear Customer," & vbNewLine & vbNewLine & _ "Please contact us on or before " & Format(Now, "mmmm") For Each FileCell In rng.SpecialCells(xlCellTypeConstants) If Trim(FileCell) < "" Then If Dir(FileCell.Value) < "" Then .AddAttachment 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 If End Sub- Hide quoted text - - Show quoted text - No, I open the file use Excel in my working place's computer (Office), it cannot work, but at my Home's computer, it can work. It is because my company using "MS Exchange" so I cannot use my Macro? Did you know how can set the code about this, I have 2 MS Exchange email account, how can I login with fix user name? Thanks! We use MS Exchange server too at work in Outlook, I do not have two accounts set up but I have my one and other mailboxes that I have permission to send mail from. We have macros sending workbooks via email that work fine using the Outlook Object Model or routing slips (as long as the file is not opened in IE window from Intranet). Although since the recent upgrade to XP & Office 2003 the users get those annoying pop up warnings. However you are not using the Outlook Object model to send the email, you are using CDO. In fact, looking again you seem to be trying to use a mixture of Both methods!! - you set OutApp to Outlook.Application then createobject CDO.message I think this is why you are having problems! recommened reading:- http://www.rondebruin.nl/sendmail.htm |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot send mail in Office
On 10 9 , 10 32 , Damon wrote:
" wrote: On Oct 8, 11:47 pm, Damon wrote: Random question (with reason) Are you opening the Excel file from a link on an intranet page at work? I ask because I have a similar issue where the 'email' part of our excel files falls over *if* I open them from hyperlinks on the intranet - if I open the file from the network via windows explorer the macros work fine. I tried a few different methods of emailing from VBA but they all had an error somewhere in the code if opened via the intranet. The work around is to get Excel files to open in Excel. Microsoft explain he- http://support.microsoft.com/?scid=162059 So I am currently trying to get our systems guys to apply one of those 'Fixes' globally to all the PCs. " wrote: I already test my marco at home, it work. But in Office, cannot send out and shown error message "Run-time error '-2147220960 (80040220)' The "SendUsing" configuration value is invalid." Office is use "MS exchange", don't know how do set the code for loggin user name/password (because i have 2 email account). below is sample it work at home "SMTP" Sub Send() myMsg = "Send out email Now?" myTitle = "Send out" myBtn = MsgBox(myMsg, vbOKCancel + vbExclamation, myTitle) If myBtn = 1 Then 'Working in 2000-2007 Dim iMsg As Object Dim iConf As Object Dim sh As Worksheet Dim cell As Range, FileCell As Range, rng As Range ' Dim Flds As Variant With Application .EnableEvents = False .ScreenUpdating = False End With Set sh = Sheets("Lookup") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConst ants) '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) If Val(Application.Version) = 12 Then If wb.FileFormat = 51 And wb.HasVBProject = True Then MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _ "Save the file first as xlsm and then try the macro again.", vbInformation Exit Sub End If End If With Application .ScreenUpdating = False .EnableEvents = False End With Set iMsg = CreateObject("CDO.Message") 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") = "smtpo.hkbn.net" ' .Item("http://schemas.microsoft.com/cdo/configuration/ smtpserverport") = 25 ' .Update ' End With With iMsg Set .Configuration = iConf .To = cell.Value .BCC = "" .Subject = cell.Offset(0, -1).Value & " Subject linel" & " - " & Format(Now, "mmmm yy") .TextBody = "Dear Customer," & vbNewLine & vbNewLine & _ "Please contact us on or before " & Format(Now, "mmmm") For Each FileCell In rng.SpecialCells(xlCellTypeConstants) If Trim(FileCell) < "" Then If Dir(FileCell.Value) < "" Then .AddAttachment 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 If End Sub- Hide quoted text - - Show quoted text - No, I open the file use Excel in my working place's computer (Office), it cannot work, but at my Home's computer, it can work. It is because my company using "MS Exchange" so I cannot use my Macro? Did you know how can set the code about this, I have 2 MS Exchange email account, how can I login with fix user name? Thanks! We use MS Exchange server too at work in Outlook, I do not have two accounts set up but I have my one and other mailboxes that I have permission to send mail from. We have macros sending workbooks via email that work fine using the Outlook Object Model or routing slips (as long as the file is not opened in IE window from Intranet). Although since the recent upgrade to XP & Office 2003 the users get those annoying pop up warnings. However you are not using the Outlook Object model to send the email, you are using CDO. In fact, looking again you seem to be trying to use a mixture of Both methods!! - you set OutApp to Outlook.Application then createobject CDO.message I think this is why you are having problems! recommened reading:- http://www.rondebruin.nl/sendmail.htm- - - - How can I amend above the code? Is it delete Set OutApp to Outlook.appliation? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot send mail in Office
Check out my CDO page and see if this is working for you
http://www.rondebruin.nl/cdo.htm Post back if you need more help to change my Outlook example to CDO http://www.rondebruin.nl/mail/folder2/files.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm wrote in message ups.com... On 10 9 , 10 32 , Damon wrote: " wrote: On Oct 8, 11:47 pm, Damon wrote: Random question (with reason) Are you opening the Excel file from a link on an intranet page at work? I ask because I have a similar issue where the 'email' part of our excel files falls over *if* I open them from hyperlinks on the intranet - if I open the file from the network via windows explorer the macros work fine. I tried a few different methods of emailing from VBA but they all had an error somewhere in the code if opened via the intranet. The work around is to get Excel files to open in Excel. Microsoft explain he- http://support.microsoft.com/?scid=162059 So I am currently trying to get our systems guys to apply one of those 'Fixes' globally to all the PCs. " wrote: I already test my marco at home, it work. But in Office, cannot send out and shown error message "Run-time error '-2147220960 (80040220)' The "SendUsing" configuration value is invalid." Office is use "MS exchange", don't know how do set the code for loggin user name/password (because i have 2 email account). below is sample it work at home "SMTP" Sub Send() myMsg = "Send out email Now?" myTitle = "Send out" myBtn = MsgBox(myMsg, vbOKCancel + vbExclamation, myTitle) If myBtn = 1 Then 'Working in 2000-2007 Dim iMsg As Object Dim iConf As Object Dim sh As Worksheet Dim cell As Range, FileCell As Range, rng As Range ' Dim Flds As Variant With Application .EnableEvents = False .ScreenUpdating = False End With Set sh = Sheets("Lookup") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConst ants) '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) If Val(Application.Version) = 12 Then If wb.FileFormat = 51 And wb.HasVBProject = True Then MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _ "Save the file first as xlsm and then try the macro again.", vbInformation Exit Sub End If End If With Application .ScreenUpdating = False .EnableEvents = False End With Set iMsg = CreateObject("CDO.Message") 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") = "smtpo.hkbn.net" ' .Item("http://schemas.microsoft.com/cdo/configuration/ smtpserverport") = 25 ' .Update ' End With With iMsg Set .Configuration = iConf .To = cell.Value .BCC = "" .Subject = cell.Offset(0, -1).Value & " Subject linel" & " - " & Format(Now, "mmmm yy") .TextBody = "Dear Customer," & vbNewLine & vbNewLine & _ "Please contact us on or before " & Format(Now, "mmmm") For Each FileCell In rng.SpecialCells(xlCellTypeConstants) If Trim(FileCell) < "" Then If Dir(FileCell.Value) < "" Then .AddAttachment 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 If End Sub- Hide quoted text - - Show quoted text - No, I open the file use Excel in my working place's computer (Office), it cannot work, but at my Home's computer, it can work. It is because my company using "MS Exchange" so I cannot use my Macro? Did you know how can set the code about this, I have 2 MS Exchange email account, how can I login with fix user name? Thanks! We use MS Exchange server too at work in Outlook, I do not have two accounts set up but I have my one and other mailboxes that I have permission to send mail from. We have macros sending workbooks via email that work fine using the Outlook Object Model or routing slips (as long as the file is not opened in IE window from Intranet). Although since the recent upgrade to XP & Office 2003 the users get those annoying pop up warnings. However you are not using the Outlook Object model to send the email, you are using CDO. In fact, looking again you seem to be trying to use a mixture of Both methods!! - you set OutApp to Outlook.Application then createobject CDO.message I think this is why you are having problems! recommened reading:- http://www.rondebruin.nl/sendmail.htm- - - - How can I amend above the code? Is it delete Set OutApp to Outlook.appliation? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bypass: A program is trying to send mail using Item.Send prompt | Excel Discussion (Misc queries) | |||
Using 2007 Office EXCEL or WORD "SEND" Results in General Mail Fai | Excel Discussion (Misc queries) | |||
A program is trying to send mail using Item.Send | Excel Programming | |||
Office 2003 Send To option of mail as attachment | Excel Discussion (Misc queries) | |||
Send mail problem in Office 2003 | Excel Programming |