Re: Cannot send mail in Office

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

"beancurdjelly2003@xxxxxxxxxxxx" wrote:

On Oct 8, 11:47 pm, Damon <Da...@xxxxxxxxxxxxxxxxxxxxxxxxx> 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
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.



"beancurdjelly2...@xxxxxxxxxxxx" 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(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 & " 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
.

Relevant Pages

  • Re: Cannot send mail in Office
    ... Are you opening the Excel file from a link on an intranet page at work? ... Dim iConf As Object ... .ScreenUpdating = False ...
    (microsoft.public.excel.programming)
  • problems with importing an image
    ... we have an intranet page that links up with Excel. ... our main server the image does not load properly. ... Dim curWks As Worksheet ...
    (microsoft.public.excel.misc)
  • Re: Cannot send mail in Office
    ... the file from the network via windows explorer the macros work fine. ... error somewhere in the code if opened via the intranet. ... Dim iConf As Object ... However you are not using the Outlook Object model to send the email, ...
    (microsoft.public.excel.programming)
  • Re: Output form to excel with range ????
    ... here is a long post with lots of useful code for exporting to excel. ... Dim varGetFileName As Variant 'File Name with Full Path ... Set objXLws = objXLWkb.ActiveSheet ... For Each cell In objXLws.Range ...
    (microsoft.public.access.formscoding)
  • Re: Vb6 + Sql + Excel
    ... >> data into Excel using Jet provider, ... >>> Dim cString As String ... >>> Dim MerchantMonthly As String ... >>> Dim formatcell As String ...
    (microsoft.public.vb.database.ado)