Outlook automation problem

diddi

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
greets all

below is a sub which sends an attachment via Outlook. Its a rip off from Ron Debruin (thx).
All works as expected except that the email is sent to the Outlook outbox, but does not actually get sent unless I open Outlook. How can i send without opening Outlook?
TIA

Code: 
Sub SendMail(ToRecipient As String, AttachDesc As String)
    Dim OlApp As Object, OlMail As Object

    Set OlApp = CreateObject("Outlook.Application")
    Set OlMail = OlApp.createitem(olmailitem)
    
    With OlMail
        .Recipients.Add ToRecipient
        .Subject = AttachDesc & " from ----"
        .Attachments.Add "D:\RAPDFs\" & AttachDesc
        .Send
    End With
    
    Set OlMail = Nothing
    Set OlApp = Nothing
End Sub
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
check out...

Code: 
OlApp.Session.Accounts.Session.Logon (Parms)
OlApp.Session.SendAndReceive (False)
B4 your set to nothings.
 
Upvote 0
Hi Pete
I use this in one of my sheets.....edit to suit
Code: 
Sub Send_Email()
Dim Email_Subject, Email_Send_From, Email_Body As String, i As Integer
Dim Mail_Object, nameList As String, o As Variant
        Email_Send_From = ""
         For i = 7 To 39
    If Sheets("SHEETNAME").Range("I7").Value <> "" Then
        nameList = nameList & ";" & Sheets("SHEETNAME").Range("I" & i).Value
    End If
Next
        Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
            .Subject = "SUBJECT TEXT "
            .To = nameList
            .Body = "BODY TEXT
            .Attachments.Add "FILEPATH NAME AND EXTENSION" 'ActiveWorkbook.FullName
            .Send
End With
        MsgBox "E-mail successfully sent", 64
        Application.DisplayAlerts = False
End Sub
 
Upvote 0
@tweedle
much googling turns up that everyone has the same problem and without using "redemption" which doesnt work all the time anyway, it cant be done with this method

@MM
ill give it a whirl now

thx
 
Upvote 0
@MM
does this work without having outlook open?
 
Upvote 0
no luck with MM code.
still gets stuck in outlook outbox.

using outlook 2003/excel 2003 XP pro SP3

might have to try it a different way...

@MM new emails :)
 
Upvote 0
How odd
I see nothing...I click my button on the spreadsheet for this code...and it's done .
Then the message popup..."Message sent"
It's not stopping at the attachment part is it ?
 
Upvote 0
no, but have you got outlook minimised in your tray? it is running if its in the tray. i have outlook express as default client, so outlook is not running at all.
 
Upvote 0
No, I'm not minimised...it was closed.
But I'm not using Express, so that could be the issue.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Issue opening Outlook with VBA
Replies
1
Views
264
rlv01
R
JeffGrant
  • Solved
Excel talking to Outlook throws 462 errors
Replies
7
Views
307
JeffGrant
JeffGrant
M
  • Question Question
code to include a column or more based on checkbox
Replies
6
Views
139
mecerrato
M
M
  • Question Question
Add text before pasting pic in outlook
Replies
2
Views
79
DanteAmor
DanteAmor
B
  • Question Question
Macro to open a new email via Outlook
Replies
3
Views
770
skillilea
S

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top