Cannot send mail in Office

[beancurdjelly2003]

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(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)



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

 

[Damon]

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
here:-

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.

 

[beancurdjelly2003]

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
here:-

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.



























- 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!

 

[Damon]

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

 

[beancurdjelly2003]

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?

 

[Ron de Bruin]

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