Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Send mail from Excel, and suppress message 5

Status
Not open for further replies.
GlennUK

GlennUK

MIS
Apr 8, 2002
2,936
0
36
GB
Hi all,

I am trying to write some VBA to send mail from within Excel ( version 2003 ), except that using the Send command, Outlook asks for user confirmation:
A program is trying to automatically send e-mail on your behalf.
Do you want to allow this?

If this is unexpected, it may be a virus and you should choose "No".
Click to expand...
... with Yes, No, and Help buttons.

Is it possible to send mail from Excel without user intervention ( it would be nice to have this running automatically on a machine churning out emails once a day when certain criteria are true ).



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.
 
Sort by date Sort by votes
Hi Glenn - couple of FAQs on this. there are 3 options really

1: Apply "redemption" patch to Outlook server (generally banned by IT depts)
2: Use a 3rd party app that clicks the ok box for you (this is the on that I use) faq707-5230
3: Code to Outlook itself but I don't have any experience as to how robust that is faq707-4334

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Thanks Geoff ... didn't realise that those FAQs where there ( it's not stuff I frequently ask, har har ). Will have a read and discuss with the IT people here.

Many thanks again.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.
 
Upvote 0 Downvote
Do a google search for outlook object model guard

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
No worries Glenn - I use the express click yes routine because it's easy !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote

I have a small VB 6.0 app that I wrote to send e-mail's. I use it to send myself (and a couple of my co-workers) a reminder about time sheet day (every 2 weeks) and some other 'stuff'. It is run from Sheduled Tasks on XP machine.

I do some checking in ORACLE database sometimes, too.

No prompts, no message boxes. Works like a charm.

Have fun.

---- Andy
 
Upvote 0 Downvote
Andy,

What are the chances of getting you to post that code? It sounds like something a lot of us could use from time to time.

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long
 
Upvote 0 Downvote
Sure,

As I said, it is from VB 6.0, but I hope you can make it work in VBA as well.

Add a reference to Microsoft CDO for Windows 2000 Library

Code: 
Public Sub SendAMessage(strFrom As String, strTo As String, _
    strCC As String, strSubject As String, strTextBody As String, _
    Optional strBcc As String)

Set objMessage = New CDO.Message

With objMessage
    .From = strFrom
    .To = strTo
    If Len(Trim$(strCC)) > 0 Then
        .CC = strCC
    End If
    If Len(strBcc) > 0 Then
        .BCC = strBcc
    End If[green]
    ''' On behalf of
    '.Sender = "CherylW@msn.com"[/green]
    .Subject = strSubject
    .TextBody = strTextBody
    With .Configuration.Fields
        .Item(CDO.cdoSMTPServer) = "NTSMTP.ABC.XYZ.LAN"
        .Item(CDO.cdoSMTPServerPort) = 25
        .Item(CDO.cdoSendUsingMethod) = CDO.cdoSendUsingPort
        .Item(cdoSMTPConnectionTimeout) = 10
        .Update
    End With
    .Send
End With

Set objMessage = Nothing

End Sub

Have fun.

---- Andy
 
Upvote 0 Downvote
Hi Geoff:
No worries Glenn - I use the express click yes routine because it's easy !!
Click to expand...

yes, you're right, it is easy!

Thanks, and a purply pointy thing ===> * for that help.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
Run PowerShell Commands from Excel VBA
Replies
14
Views
676
dhookom
dhookom
remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
177
strongm
strongm
huiettcm
  • Locked
  • Question
Excel UserForm Window 'Focus' Event Listener 1
Replies
14
Views
169
huiettcm
huiettcm
remeng
  • Locked
  • Question
Getting results from a dynamic dimension array
Replies
2
Views
147
Andrzejek
Andrzejek
feipezi
  • Locked
  • Question
How to pull data from MS Access DB and place on an Excel spreadsheet with WHERE clause? 2
Replies
10
Views
154
feipezi
feipezi

Part and Inventory Search

Sponsor

Back
Top