Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Send Email Using CDO

I have been reading about using CDO to send and email from excel. I also have tried the Ron de Bruin's Website. Here is what he gives as an example. When i change the info and try it. I get an error on the code

	VB:
	
.send 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
section of his example. Is there anyone that can help me.

http://www.rondebruin.nl/cdo.htm


	VB:
	
 'The example will send a small text message
 'You must change four code lines before you can test the code
 
 '.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "Full GMail mail address"
 '.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "GMail password"
 
 'Use your own mail address to test the code in this line
 '.To = "Mail address receiver"
 
 'Change YourName to the From name you want to use
 '.From = """YourName"" "
 
 'If you get this error : The transport failed to connect to the server
 'then try to change the SMTP port from 25 to 465
 
Sub CDO_Mail_Small_Text_2() 
    Dim iMsg As Object 
    Dim iConf As Object 
    Dim strbody As String 
    Dim Flds As Variant 
     
    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/smtpusessl") = True 
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "Full GMail mail address" 
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "GMail password" 
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" 
         
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 
        .Update 
    End With 
     
    strbody = "Hi there" & vbNewLine & vbNewLine & _ 
    "This is line 1" & vbNewLine & _ 
    "This is line 2" & vbNewLine & _ 
    "This is line 3" & vbNewLine & _ 
    "This is line 4" 
     
    With iMsg 
        Set .Configuration = iConf 
        .To = "Mail address receiver" 
        .CC = "" 
        .BCC = "" 
         ' Note: The reply address is not working if you use this Gmail example
         ' It will use your Gmail address automatic. But you can add this line
         ' to change the reply address  .ReplyTo = "Reply@something.nl"
        .From = """YourName"" " 
        .Subject = "Important message" 
        .TextBody = strbody 
        .Send 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Post your answer or comment

comments powered by Disqus
Hi all,

I have trouble sending email using CDO in XP. It worked fine when I used Windows 2000. The attachment will get corrupted and the size is only 10 byte. Here is my code. Please help me to find the error and solution.


	VB:
	
 Send() 
     
    Dim ocDont As New CDO.Message 
    Dim iConf  As New CDO.Configuration 
    Dim  sWk_Name, sWk_Name1 As String 
    Set Flds = iConf.Fields 
    Flds(cdoSendUsingMethod) = cdoSendUsingPort 
    Flds(cdoSMTPServer) = "192.168.5.6" 
    Flds.Update 
     
     
    ActiveWorkbook.SaveAs "C: REPORT STATUS.XLS" 
     
    sWk_Name = ActiveWorkbook.FullName 
    sWk_Name1 = ActiveWorkbook.Name 
    ActiveWorkbook.Close 
     
    With ocDont 
        Set .Configuration = iConf 
        .From = "a@yahoo.com" 
        .To = "b@yahoo.com" 
        .Subject = sWk_Name1 
        .AddAttachment sWk_Name 
        .Send 
    End With 
    Set ocDont = Nothing 
    Set iConf = Nothing 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Please help , Thanks

Hi people, I'm trying to get Excel to run a macro to send me an email via CDO using google's SMTP, I'm not very familiar with VBA nor excel so I searched around and found this code:

Sub Send_Email_Using_CDO()
Dim CDO_Mail_Object As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim Email_Subject, Email_Send_From, Email_Send_To, Email_Cc, Email_Bcc, Email_Body As String

Email_Subject = "Trying to send email using CDO"
Email_Send_From = "email@gmail.com"
Email_Send_To = "email@gmail.com"
Email_Cc = "email@gmail.com"
Email_Bcc = "email@gmail.com"
Email_Body = "Congratulations!!!! You have successfully sent an e-mail using CDO !!!!"

Set CDO_Mail_Object = CreateObject("CDO.Message")

On Error GoTo debugs
Set CDO_Config = CreateObject("CDO.Configuration")
        CDO_Config.Load -1
        Set SMTP_Config = CDO_Config.Fields
With SMTP_Config
    .Item("http://schemas.Microsoft.Com/cdo/configuration/smtpusessl") = True
    .Item("http://schemas.Microsoft.Com/cdo/configuration/smtpauthenticate") = 1
    .Item("http://schemas.Microsoft.Com/cdo/configuration/sendusername") = "email@gmail.com"
    .Item("http://schemas.Microsoft.Com/cdo/configuration/sendpassword") = "password"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    'Put your server name below
   .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.Gmail.Com"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
    .Update
End With
       
With CDO_Mail_Object
    Set .Configuration = CDO_Config
End With

CDO_Mail_Object.Subject = Email_Subject
CDO_Mail_Object.From = Email_Send_From
CDO_Mail_Object.To = Email_Send_To
CDO_Mail_Object.TextBody = Email_Body
CDO_Mail_Object.cc = Email_Cc                      'Use if needed
CDO_Mail_Object.BCC = Email_Bcc                    'Use if needed
'CDO_Mail_Object.AddAttachment FileToAttach        'Use if needed
CDO_Mail_Object.send

debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub
It returns the error: "The transport failed to connect to server".

I tried changing the port to 25, 587, does not work either, tried other similar codes and got similarly strange results. POP is enabled in my gmail account. Can you guys tell me what's wrong with it?

Much thanks.

Hello,

I got worksheet called "Customers", colum A contains customer's name, B contains email-address, C contains name of the workbook (that this customer receives)

Workbooks are named "Book 1", "Book 2", "Book 3" etc. and are stored into "C:ExcelCustomersWorkbooks"

Point is that using this workbook called "Customers" I'm able to send multiple workbooks to wanted user. (Our company's using windows live mail).
Like shown below, when I press commandbutton "Send", Jimmy receives Book 1, Mike receives Book 1 and Book 2, and Jennifer receives Book 1 and Book 3.

Jimmy ---- jimmy@email.com ---- Book 1 [SEND]
Mike ---- mike@email.com ---- Book 1 [SEND]
Mike ---- mike@email.com ---- Book 2 [SEND]
Jennifer ---- jenn@email.com ---- Book 1 [SEND]
Jennifer ---- jenn@email.com ---- Book 3 [SEND]

I'm not familiar with CDO-codes so I would really appreaciate help with this. I attached example-workbook

hi
How can i send email to a lotus notes server or other email servers
using excel macro by clicking a button.
I read in the newsgroups to use SendKeys or CDO but is there any
examples on how to do that?
thanks

Hi All,

I am looking to send EMail to a number of Clients, derived from a Query. It would be great if it could automatically send the Emails depending on the date (e.g. send mail if today's date is 14th October. The other problem that I have is that the mail has to be sent using Outlook Express and not Outlook. I have code for Outlook, but not Outlook Express.

Thanks in advance for any help.
Regards,

Bill

Hi,

I have search on this forum regarding sending email on excel using outlook email application. I would like to ask if is it possible to use other email applications like AOL when sending email thru excel? I have attached a sample workbook .

regards,

stoey

hi

I'm using a typical code that sends email using CDO objects.

The code has an ".addattachment" method to attach file.

This code is basically in a loop that sends 1 email after another by getting
email addresses from a range of cells.

The objective is to send one attachment per email.

The problem is the .addattachment method keeps stacking up files from
previous attachments as the code loops. The first email would get its
attachment; 2nd email would get its attachment plus that of the first email,
and so on.

How can I clear out this .addattachment method before the next loop ?

Thanks
Steve

Hi guys, was hoping someone could help me on this. Looked around the website but could not find anything specific on my issue.

I'm trying to create an excel macro to send emails to various users. The website listed below helps greatly, but I need to be able to send from a group inbox instead of my own. Is there anyway for me to add a line to specify which inbox am I sending from?

Thanks in advance!

Example from this website: http://spreadsheetpage.com/index.php...il_from_excel/

Here is the code used:

Private Declare
Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As LongSub SendEMail()
    Dim Email As String, Subj As String
    Dim Msg As String, URL As String
    Dim r As Integer, x As Double
    For r = 2 To 4 'data in rows 2-4
'       Get the email address
        Email = Cells(r, 2)
        
'       Message subject
        Subj = "Your Annual Bonus"

'       Compose the message
        Msg = ""
        Msg = Msg & "Dear " & Cells(r, 1) & "," & vbCrLf & vbCrLf
        Msg = Msg & "I am pleased to inform you that your annual bonus is "

        Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf
        Msg = Msg & "William Rose" & vbCrLf
        Msg = Msg & "President"
        
'       Replace spaces with %20 (hex)
        Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
        Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
                
'       Replace carriage returns with %0D%0A (hex)
        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")        '       Create the URL
        URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg  
     

'       Execute the URL (start the email client)
        ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

'       Wait two seconds before sending keystrokes
        Application.Wait (Now + TimeValue("0:00:02"))
        Application.SendKeys "%s"
    Next r
End Sub


Does anyone know if it is possible to get the name of the local smtp or exchange server so you can send email using cdo from excel vba. I have all the code for the cdo send mail and that all works fine as long as I manually code the name of the mail server in, but this is a workbook we want to send out for customers to complete a worksheet and I have a button on the worksheet they would click on to send it back to us automatically.
Thanks in advance for any help.

Hi,

If you have a database as follows:

******** ******************** ************************************************************************>Microsoft Excel - Company details.xls___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB1=
BCDE1Company NameContact PersonEmail 2SL MediaFerdiferdi@slmedia.co.za 3Belay Rapid Resources aja@belay.co.za Sheet2Hyper Lynks Address is here ...
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Please assist in creating a macro or something to automatically send emails using the emails in the data base.

Thank you

I want to send an email to clients , but clients email address list is in excel file.

Is there any way to send email using list. i don't want to use macro.

I am using excel 2003

Regards
Rahul

Does anybody know how to send email using Excel.

What I wanna do is to keep the email address list on sheet 1 and the email content on sheet 2.

Then have a button on sheet 1 to send everyone on the list the same email.

Thanks.

Hello all,

I'm trying to put together some code that sends emails using outlook, taking the addressees, subject and email body from cells within the spreadsheet.

This is what I have:
Sub SendEMail()

    Dim MyOutlook As Outlook.Application
    Dim MyMail As Outlook.MailItem
    Dim Subject As String
    Dim Message As String
           
    Subject = Range("Subject").Value
    
    ' If there's no subject, exit:
    If Subject = "" Then
    MsgBox "There is no subject line on the Email Details tab."
    Exit Sub
    End If
       
    Message = Range("Message").Value
    
    ' If there's no Message, exit:
    If Message = "" Then
    MsgBox "There is no Message on the Email Details tab."
    Exit Sub
    End If
          
    ' Open outlook instance:
    Set MyOutlook = New Outlook.Application
       
    'Now loop through each mail recipient in MailList, create the email and send it:
    
    For Each c In Range("MailList").Cells
        
        If c.Value = "" Then
            Set MyMail = Nothing
            Set MyOutlook = Nothing
            Exit Sub      'Exits when it reaches a blank entry.
        End If
        
        Set MyMail = MyOutlook.CreateItem(olMailItem)
        MyMail.To = c.Value
        MyMail.Subject = Subject
        MyMail.Body = Message
'        MyMail.Attachments.Add "c:myfile.txt", olByValue, 1, "My Displayname"
'  MyMail.Display
        MyMail.Send

        'To view the mail instead of send it use "MyMail.Display"
    Next
The problem I have is that when it gets to "MyMail.Send" I get "Run-time error '287', Application-defined or Object-defined error." If I substitute the "MyMail.Send" line with "MyMail.Display", the emails open and have been populated fine. So it just seems that the Send method is not being recognised.

I've found similar posts here and elsewhere, but nothing that deals specifically with this example. Any advice woud be be gratefully received...

(Originally posted this as a reply on a SOLVED thread - thought it better to post as a new one - apologies for double posting).

I have tried a number of things, but have not been able to figure it out. It may not be available with this particular code, but I am not sure what limitations this code has. Does anyone know how to include the contents of a particular cell in the subject line? Currently the subject line says "Notification." What I am looking for is for the subject to say "Notification - John Doe" where John Doe would be the contents of cell D13.

Sub SendEmail()
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object

    Set rng = Nothing
    On Error Resume Next
    'Only the visible cells in the selection
    Set rng = Selection.SpecialCells(xlCellTypeVisible)
    'You can also use a range if you want
    Set rng = Sheets("Sheet1").Range("D6:D13").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected" & _
               vbNewLine & "please correct and try again.", vbOKOnly
        Exit Sub
    End If

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = "test@test.com"
        .CC = ""
        .BCC = ""
        .Subject = "Notification"
        .HTMLBody = RangetoHTML(rng)
        .Send   'or use .Display
    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Function RangetoHTML(rng As Range)
' Working in Office 2000-2007
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook
 
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"
 
    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With
 
    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With
 
    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")
 
    'Close TempWB
    TempWB.Close savechanges:=False
 
    'Delete the htm file we used in this function
    Kill TempFile
 
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function


I am able to send email with CDO but is there a way to simply save it as a
DRAFT email rather than sending it straight away to the recipient

e.g
Dim cdoConfig As CDO.Configuration
Dim cdoMessage As CDO.Message

..Send is an option but I do not see a .Save as an option.

Thanks in advance for any help.

Hi All,

I would like some help generating some code as I wish to send an attachment (located at c:test.txt) with every email I send from Excel 2007. I have attached the file used to send emails using the UserForm method.

Cheers
Ash

I have a 'working' macro to send emails from Excel. However, I have two
issues remaining:
1. The amount of time per email is about 5 seconds. The bottleneck seems to
be the Outlook warning that 'another program is trying to access Outlook.'
The code below uses Express ClickYes, which waives the warning. I had also
tried sending email using CDO code, but could not get it to work with my
email and/or proxy server. Do you see a way to speed things up?

2. I would like to add code that would turn off (deselect) the 'Immediate
Send' option in Outlook. Can anyone help with that syntax please?

Thanks in advance, John

*************** Code follows ******************
Sub GroupProjects()
Dim wnd As Long
Dim uClickYes As Long
Dim Res As Long
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range
Dim SigString As String, Cmts As String, SBJ As String
Dim Signature As String
Dim Assgn As String, TxtBody As String, Finished As Boolean
Dim StuID As Variant, rng As Range, rng1 As Range
Dim PtsToDate As Variant, MaxPts As Variant, GradeToDate As String, Nick
As String
Dim Product As String, FcastVariance As Variant, FcastCalcVariance As
Variant, AssgnPts As Variant, TotAssignPts As Variant
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
' Register a message to send
uClickYes = RegisterWindowMessage("CLICKYES_SUSPEND_RESUME")

' Find ClickYes Window by classname
wnd = FindWindow("EXCLICKYES_WND", 0&)

' Send the message to Resume ClickYes
Res = SendMessage(wnd, uClickYes, 1, 0)

On Error GoTo cleanup
SigString = "C:Documents and Settings" & Environ("username") & _
"Application DataMicrosoftSignaturesxxx Formal Office.txt"
If Dir(SigString) <> "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If
Assgn = Range("B1").Value 'Name of the assignment
TotAssignPts = Range("B2") ' Max pts for the assignment
Finished = Range("B3").Value 'Whether assignment has been noted as completed
If Not (Finished) Then
MsgBox ("Assignment has not been flagged as being complete. No further
action taken.")
Exit Sub
End If
ans = MsgBox("Getting ready to send emails to all students. You should have
turned off AutoSend in Outlook. Continue?", vbYesNo)
If ans = vbNo Then Exit Sub
SBJ = "Your grade for " & Assgn
SBJ = InputBox("Enter the subject line for this email.", "Email Subject", SBJ)
Set rng = Worksheets("Summary").Range("D2:D90") 'Student ID Column for
searches
For Each cell In Range("D29:D100").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value <> "" Then
StuID = cell.Value
AssgnPts = cell.Offset(0, 1).Value 'This is the override column
Cmts = cell.Offset(0, 2).Value 'Comments
Res = Application.Match(StuID, rng, 0)
If Not IsError(Res) Then 'get summary data from Summary sheet
Set rng1 = rng(Res, 1)
PtsToDate = rng1.Offset(0, 1).Value
MaxPts = rng1.Offset(0, 2).Value
GradeToDate = rng1.Offset(0, 3).Value
Nick = rng1.Offset(0, -2)
End If
TxtBody = "Dear " & Nick & "," & vbNewLine & _
vbNewLine & "Your team's grade for the " & Assgn & " assignment
was " & AssgnPts & " points out of a possible " & TotAssignPts & "."
If Cmts <> "" Then
TxtBody = TxtBody & vbNewLine & vbNewLine & "The following
comment was noted: " & Cmts & "."
End If
TxtBody = TxtBody & vbNewLine & vbNewLine & "Including this team
assignment, you have individually accumulated a total of " & PtsToDate & "
out of a possible " & MaxPts & " points for an implied grade to date of '" &
GradeToDate & "'."
TxtBody = TxtBody & vbNewLine & vbNewLine & "Best wishes." &
vbNewLine & Signature

Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail
.To = cell.Value & "@xxxx.edu"
.Subject = "Your Grade For " & Assgn
.Body = TxtBody
.Send 'Or use Display
End With
Set OutMail = Nothing
End If
Next cell
cleanup:
Res = SendMessage(wnd, uClickYes, 0, 0)

Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
**************************************

Hi all, i have the found the following code to automate sending an email from excel without prompts ( Tested and working fine ). What i would like help with is altering this code to embed a picture of a worksheet range. Lets say the range i'd like to embed is X1:Z10. Any help would be appreciated.
Sub Send_Email_Using_CDO()
Dim CDO_Mail_Object As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim Email_Subject, Email_Send_From, Email_Send_To, Email_Cc, Email_Bcc, Email_Body As String
Email_Subject = "Trying to send email using CDO"
Email_Send_From = "databison@gmail.com"
Email_Send_To = "databison@gmail.com"
Email_Cc = "databison@gmail.com"
Email_Bcc = "databison@gmail.com"
Email_Body = "Congratulations!!!! You have successfully sent an e-mail using CDO !!!!"
Set CDO_Mail_Object = CreateObject("CDO.Message")
On Error GoTo debugs
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields
With SMTP_Config
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'‘please put your server name below
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "YOURSERVERNAME"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With
With CDO_Mail_Object
Set .Configuration = CDO_Config
End With
CDO_Mail_Object.Subject = Email_Subject
CDO_Mail_Object.From = Email_Send_From
CDO_Mail_Object.To = Email_Send_To
CDO_Mail_Object.TextBody = Email_Body
'CDO_Mail_Object.cc = Email_Cc ‘Use if needed
'CDO_Mail_Object.BCC = Email_Bcc ‘Use if needed
'‘CDO_Mail_Object.AddAttachment FileToAttach ‘Use if needed
CDO_Mail_Object.send
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub


I've been having really good luck with SendMail in Excel, but until I learn how to programmatically send email using CDO instead, I'm a little worried about knowing that SendMail was actually successful since this is all invisible to the user. My worry is really only if Outlook isn't open. I've tested it on my workstation and found that despite what I've read, at least at my company outlook doesn't need to be open for sendmail to work in Excel. I even tried logging in fresh, firing up Excel to send a test email, closing Excel, restarting my workstation 15 minutes later, and then opening Outlook, and every time the email is in my inbox with a time stamp of when it was actually sent.

But--just to be sure, I'd like to code something to verify it.

Is it easy to read the user's sent mail folder, even if Outlook is closed, to verify send was successful? Or is there a better way? Or will Excel error out every time if the send wasn't successful?

Thanks!

Hello Excel Experts:

Urgent help is required from you IT guys. I need to send emails
silently within Excel using VBA, and the following code doesn't work
for me (the code is found from PaulSadowski.com):

Sub Mail_Small_Text_CDO()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim Flds As Variant

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/
smtpauthenticate") = cdoBasic
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/
sendusername") = "
.Item("http://schemas.microsoft.com/cdo/configuration/
sendpassword") = "mypassword"
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpconnectiontimeout") = 60

.Update
End With
'flds.Fields("urn:schemas:httpmail:importance") = 2
'flds.Fields("urn:schemas:mailheader:X-Priority") = 1
Flds.Update

strbody = "Hi the testing from CDO" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

With iMsg
Set .Configuration = iConf
.To = "
.CC = ""
.BCC = ""
.From = "Steineke"
.Subject = "Important message from Excel CDO"
.TextBody = strbody
.Send
End With

Set iMsg = Nothing
Set iConf = Nothing
End Sub

The intention of above sub is to compose an email and send it from
Gmail SMTP server through Excel VBA to my yahoo's email account. The
Excel workbook is running in a machine which is connected to internet
via either dial up or intranet connection, but without any email
client program installed (at least we don't care). The fwllowing error
code is displayed when .send is executed:

Run-time error '-2147220973(80040213)':
The transport failed to connect to the server.

I do have a gmail account and a yahoo account. I checked gmail help
about how to set the settings for sending and receiving emails. The
computer is connected to internet occasionally through dial-up
connection, and we want the VBA to send an email with some data back
to the headquarter without much intervention of human beings.

How to get it work? I'm not a good programmer with some knowledge
about VB. Your comments are highly appreciated.

Ken

I am trying to email a range of cells from my workbook. I have found some code here on OZGrid on how to send a message using CDO. The macro works great except I cannot figuer out how to put my range of cells into the body of the email. Basically I have been cutting and pasting into the body of the email manually and would like to automate this. Please let me know if you have any suggestions.
Thanks.


	VB:
	
 ted() 
     
    Dim iMsg As Object 
    Dim iConf As Object 
    Dim Flds As Variant 
    Dim mybook As Workbook 
    Dim body 
     
     
    Set mybook = ThisWorkbook 
    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") = "mail.youknowwhere.com" 
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 ' usually 25
        .Update 
    End With 
     
     
     
    With iMsg 
        Set .Configuration = iConf 
        .To = "email@youknowwhere.com" 
        .CC = "" 
        .BCC = "" 
        .From = "email@youknowwhere.com" 
        .Subject = "TEST" 
        .HTMLbody = Workbooks("Morning mark check").Sheets("Data").Range("A1:F92") 
        .Send 
    End With 
     
    Set iMsg = Nothing 
    Set iConf = Nothing 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi All,

I'm trying to create a macro that will display an email message with the "to" field populated with email addresess in a dynamic named range, and with the activeworkbook attached. So far I've written the following code. The problem is that the following sends the email; I just want the message to pop up. I want to avoid CDO if possible because of STMP issues...

thank you!


	VB:
	
 
 
Sub SendActiveWorkbook() 
    Dim MyArr As Variant 
    MyArr = range("Me") 
    ActiveWorkbook.Sendmail Recipients:=MyArr, _ 
    Subject:="Loan Exception Report " & Format(Date, "dd/mm/yy") 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi

I have a script that saves a file using yesterdays date onto a network drive, using the VBA equivalent of network days and weeks to save the file in the following format: Daily Claims Stats - Week 33 - Monday August 11 2008 or
Daily Claims Stats - Week 32 - Friday August 08 2008. - this part works fine, but when i try to use the CDO mail method I can only manage to send the email without the attachment.

The idea is that this all takes place with one button click, the file is saved and emailed to an email group.

My script is as follows:

Sub test()
 
    Dim vbMonth As Long
    vbMonth = Format(Date, "mm")
    Dim Today
    Dim vbWeekday
    myWeekday = Weekday(Date)
    If myWeekday = 2 Then
        Today = Format(Date - 3, "dddd mmmm dd yyyy")
    Else
        Today = Format(Date - 1, "dddd mmmm dd yyyy")
    End If
    
    Dim vbWeeknum As Long
    vbWeeknum = Format(Date, "ww", 2)
    myWeekno = vbWeeknum
    If myWeeknum = 2 Then
        Weeknum = Format(vbWeeknum - 3, "dd")
    Else
        Weeknum = Format(vbWeeknum - 1, "dd")
    End If
    
    
    Folder_Month = (vbMonth)
    Folder_Name = (" Daily Claims Stats -" & " Week " & vbWeeknum & " - " &
Today)

    Dim strNewFolderName As String
    strNewFolderName = MonthName(Format(Date, "mm")) & " " & Format(Date, "yy")
    If Len(Dir("c:temp" & strNewFolderName, vbDirectory)) = 0 Then
       MkDir ("c:temp" & strNewFolderName)
    End If
  
  ActiveWorkbook.SaveAs ("c:temp") & strNewFolderName & "" & Folder_Name &
".xls"



' This part of the code works well except for the objMsg.AddAttachment object.....

Dim objMsg As Object
Set objMsg = CreateObject("CDO.Message")

objMsg.Subject = "Sample CDO Message"
objMsg.From = " no-reply@myemail.com "
objMsg.To = " myemail@myemail.com "
'objMsg.Cc = " email@removed "
'objMsg.Bcc = " email@removed "
objMsg.TextBody = "This sample message rocks! See Attachment..."
'objMsg.AddAttachment "d:temp"
'objMsg.AddAttachment "D:/webs/123/j2005_demo/temp/data.csv"
'objMsg.AddAttachment (ThisWorkbook.Path & "/" & Replace(Replace(shtData.Range("AC2").Value,
".", ""), "/", "") & " " & Format(Date, "mm-dd-yyyy")
& ".xls")
'objMsg.AddAttachment (ThisWorkbook.Path & "/" & ".xls")
objMsg.Send

Set objMsg = Nothing


End Sub
I have seen the sendmail option, but i don't want the pop up and have read Ron de Bruin but cannot seem to work out the sending of a file into an email without opening outlook and/or the file again and sending it out in that manner.

I was wondering if this code was the key?? to use a virtual workbook path??
'objMsg.AddAttachment (ThisWorkbook.Path & "/" &
Replace(Replace(shtData.Range("AC2").Value, ".", ""), "/", "") & "
" & Format(Date, "mm-dd-yyyy") & ".xls")
'objMsg.AddAttachment (ThisWorkbook.Path & "/" & ".xls")
Incidently the Outlook.application method below, does not work for me, but does include
 - the ability to attach files directly thru the VBA 
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Set OutMail = OutApp.CreateItem(olMailItem)
Any ideas, would be greatly appreciated??

thanks Gareth

Hi

This is probably simple but somehow I can't seem to get it to work!!!

I have code which emails a workbook using CDO to an address hard coded in the macro. I now want to add an aditional address to the code. The additional address is entered in a cell of a worksheet before running the macro.

The code at present looks like this:

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

iConf.Load -1
Set Flds = iConf.Fields
        With Flds
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "My Server.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            .Update
        End With
        
        Dim MySubject As String
        MySubject = Range("A331").Value

    With iMsg
        Set .Configuration = iConf
        .To = "hard_coded_emai@address.Com"
        .CC = ""
        .BCC = ""
        .From = """ME"" <Me@Here.com>"
        .Subject = MySubject
        .TextBody = "Workbook Attached"
        .AddAttachment TempFilePath & TempFileName & FileExtStr
        .Send
    End With
I now want it to also send to the address contained in "uptime" worksheet cell "G4"

Thanks Guys!!!!!!!!


No luck finding an answer? You could always try Google.