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 SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

With MailOutlook. ... mail shows up in sent items without sending 1

Status
Not open for further replies.
boggsie

boggsie

Technical User
Apr 14, 2012
12
0
0
US
Ok, so a few of you most helpful members got me up and running with SMTP and a few days later, my port has been shut down by my provider. <sad face>

So, I want to move this to actually creating the messages natively in Outlook and then clicking send.

Problem is, the message does display, but when I click send, it shows up in my sent items, but neveractually gets sent.

Code: 
Option Explicit
Public Function SendDashboardMail()
    Dim Recordset As DAO.Recordset
    Set Recordset = CurrentDb.OpenRecordset("LocalDatabase")
    
        Do Until Recordset.EOF
            SendMail Recordset.Fields("sMailToAddress"), _
                Recordset.Fields("sMailCC"), _
                Recordset.Fields("sMailSubject"), _
                Recordset.Fields("sMailBody"), _
                "C:\Data\_Files\" & Recordset!sProjLabel & "_transport_" & Format(Date, "YYYY-MM-DD") & ".xls"
                Recordset.MoveNext
        Loop
    Recordset.Close
End Function
Public Sub SendMail(sToAddress, sCCAddress, sSubject, sBody, sMailAttachment)
    Dim objMessage As Object
    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
    Set appOutLook = CreateObject("Outlook.Application")
    Set objMessage = CreateObject("CDO.Message")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    
    With MailOutLook
        .Subject = sSubject
        .To = sToAddress
        .Body = sBody
        .Attachments.Add sMailAttachment
        .Display
    End With
End Sub

I think the code is good but some local security policy is preventing the item from sending, but I wanted to run it by the experts, first.

Best regards,
-boggsie
 
Sort by date Sort by votes
Get rid of this lines:
Dim objMessage As Object
Set objMessage = CreateObject("CDO.Message")

and replace this:
.Display
with this:
.Send

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
I appreciate the quick response and your sage guidance.

Unfortunately, the message still posts to my Sent Items without actually getting sent. I think you've more or less concerned that some local security policy must be interfering with the process on messages that are initiated in this manner. Even if I (using the .Display) save the message to Drafts and then open, edit and send, the message does not get sent.

Best regards,
-boggsie
 
Upvote 0 Downvote
So I went out and setup Wayne Phillips' Access/VBA to Outlook tweak and confirmed that it would send plain HTML messages. I then updated his test function to work with variables pointing to a recordset in the Access database. Again, the message appears in Sent Items but does not post to my In Box.

Code: 
Sub FnTestSafeSendEmail()
    Dim blnSuccessful As Boolean
    Dim strHTML As String
    Dim Recordset As DAO.Recordset
    Set Recordset = CurrentDb.OpenRecordset("tblDASHBOARD_DISTRIBUTION")
        
    Do Until Recordset.EOF
        
        strHTML = "<html>" & _
                   "<body>" & _
                   Recordset.Fields("sMailBody") & _
                   "</body>" & _
                   "</html>"
                blnSuccessful = FnSafeSendEmail(Recordset.Fields("sMailToAddress"), _
                                        Recordset.Fields("sMailSubject"), _
                                        strHTML, _
                                        "C:\Data\_DashboardReporting\_ReportOutput\" & Recordset!sProjLabel & "_Dashboards_" & Format(Date, "YYYY-MM-DD") & ".xls", _
                                        Recordset.Fields("sMailCC"))
                    Recordset.MoveNext
        Loop
    If blnSuccessful Then
        MsgBox "E-mail message sent successfully!"
    Else
        MsgBox "Failed to send e-mail!"
    End If
End Sub

stumped !!

Best Regards,
-boggsie
 
Upvote 0 Downvote
Oh my < embarassed >.

When I was workign on this (via SMTP) earlier in the week, I setup an In Box Rule to immediately post these message that were delivered to my In Box, to a separate folder.

I only figured this out because I sent to another address (external to the Exchange Server in question) and the message posted. So I thought, it must be getting delivered internal if is going external ... Oh no, my In Box rule is filing them.

Thanks in advance for your help and sorry for the distraction. Things are working as expected.

Best regards,
-boggsie
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Dan Stoica
  • Locked
  • Question
Problems with Foxpro dsnless linked tables
Replies
0
Views
76
Dan Stoica
Dan Stoica
Kim296
  • Locked
  • Question
Sending Gmail using vba Send error
Replies
6
Views
232
strongm
strongm
JaineyBlue
  • Locked
  • Question
DLookup with criteria 1
Replies
2
Views
99
JaineyBlue
JaineyBlue
TheAceMan1
  • Locked
  • Question
Delete from listi n XL open tab with VBA
Replies
2
Views
115
TheAceMan1
TheAceMan1
aldi07
  • Locked
  • Question
update a front-end database with a new release using vba
Replies
5
Views
99
aldi07
aldi07

Part and Inventory Search

Sponsor

Back
Top