Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sending email using cdo_message

Posted on 2013-06-20
3
Medium Priority
?
449 Views
Last Modified: 2013-06-22
I have this piece of code that is supposed to send email without outlook being opened.  Could someone clean this up so it works, I was originally trying with the Outlook application object, but that only sent email if outlook was open, I need it to be sent even if outlooks is not open.  So I am trying cdo but when it hits line
  With cdo_message
        Set .Configuration = cdo_config
    End With
   
I receive object required.

thanks in advance





Dim myString As String

myString = "Item: " & Cells(myRow, 2) & vbCrLf & vbCrLf
myString = myString & "Detail Description: " & Cells(myRow, 3) & vbCrLf & vbCrLf
myString = myString & "Detail Comments: " & Cells(myRow, 4) & vbCrLf & vbCrLf
myString = myString & "Resolution: " & Cells(myRow, 5) & vbCrLf & vbCrLf
myString = myString & "Proposal due Date: " & Cells(myRow, 6) & vbCrLf & vbCrLf
myString = myString & "Who is obtaining?: " & Cells(myRow, 7) & vbCrLf & vbCrLf
myString = myString & "Target Date: " & Cells(myRow, 8) & vbCrLf & vbCrLf
myString = myString & "Date Proposal Received: " & Cells(myRow, 9) & vbCrLf & vbCrLf
myString = myString & "Date of SignOff: " & Cells(myRow, 10) & vbCrLf & vbCrLf
myString = myString & "Date Work Completed: " & Cells(myRow, 11) & vbCrLf & vbCrLf
myString = myString & "Follow Up Date: " & Cells(myRow, 12) & vbCrLf & vbCrLf
myString = myString & "Extra Notes: " & Cells(myRow, 13) & vbCrLf & vbCrLf


    Dim OutApp As Object
    Dim OutMail As Object

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

    On Error GoTo Problem
    
   ' Change the mail address and subject in the macro before you run it.
''    With OutMail
''        .To = "dprogelhof@theapartmentgallery.com; fsturgill@theapartmentgallery.com"
''        .CC = "shicks@theapartmentgallery.com"
''        .BCC = ""
''        .Subject = "There is a issue with inspection: " & Cells(6, 3)
''        .Body = myString
''        .Attachments.Add ActiveWorkbook.FullName
''         You can add other files by uncommenting the following line.
''        .Attachments.Add ("C:\test.txt")
''         In place of the following statement, you can use ".Display" to
''         display the mail.
''        .Send
''    End With
    
    Set CD0_Mail = CreateObject("CDO.message")
    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
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "APTEMAILSERVER2.mail2.apartmentgallery.net"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    .Item(cdoSMTPAuthenticate) = 1
    .Item(cdoSendUserName) = "*****"
    .Item(cdoSendPassword) = "*****"
    .Update
    End With
    
    With cdo_message
        Set .Configuration = cdo_config
    End With
    
     With cdo_message
        .To = "dprogelhof@theapartmentgallery.com; fsturgill@theapartmentgallery.com"
        .CC = "shicks@theapartmentgallery.com"
        .BCC = ""
        .Subject = "There is a issue with inspection: " & Cells(6, 3)
        .Body = myString
        '.Attachments.Add ActiveWorkbook.FullName
        ' You can add other files by uncommenting the following line.
        '.Attachments.Add ("C:\test.txt")
        ' In place of the following statement, you can use ".Display" to
        ' display the mail.
        .Send
    End With
    
   
    Set OutMail = Nothing
    Set OutApp = Nothing
        
        

   Exit Function
Problem:
    MsgBox Err.Description
    Exit Function
                                       

Open in new window


(Edit: Redacted - Modulus_Twelve)
0
Comment
Question by:mgmhicks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
3 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 39263229
With cdo_message
        Set .Configuration = cdo_config
    End With

should be

    With CDO_Mail
        Set .Configuration = cdo_config
    End With

OM Gang
0
 
LVL 28

Accepted Solution

by:
omgang earned 2000 total points
ID: 39263253
More....

You've actually got it as
CD0_Mail <--- not the zero instead of an O.

Also need to replace it here
     With cdo_message  <---- change to CD0_Mail
        .To = "dprogelhof@theapartmentgallery.com; fsturgill@theapartmentgallery.com"
        .CC = "shicks@theapartmentgallery.com"
        .BCC = ""


Also,
        .Subject = "There is a issue with inspection: " & Cells(6, 3)
        .Body = myString

.Body is not valid.  Use .TextBody

Also, you must include a sender field
.From = "OMGang@EE.com"

OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 39263304
Try this
OM Gang


Dim myString As String

myString = "Item: " & Cells(myRow, 2) & vbCrLf & vbCrLf
myString = myString & "Detail Description: " & Cells(myRow, 3) & vbCrLf & vbCrLf
myString = myString & "Detail Comments: " & Cells(myRow, 4) & vbCrLf & vbCrLf
myString = myString & "Resolution: " & Cells(myRow, 5) & vbCrLf & vbCrLf
myString = myString & "Proposal due Date: " & Cells(myRow, 6) & vbCrLf & vbCrLf
myString = myString & "Who is obtaining?: " & Cells(myRow, 7) & vbCrLf & vbCrLf
myString = myString & "Target Date: " & Cells(myRow, 8) & vbCrLf & vbCrLf
myString = myString & "Date Proposal Received: " & Cells(myRow, 9) & vbCrLf & vbCrLf
myString = myString & "Date of SignOff: " & Cells(myRow, 10) & vbCrLf & vbCrLf
myString = myString & "Date Work Completed: " & Cells(myRow, 11) & vbCrLf & vbCrLf
myString = myString & "Follow Up Date: " & Cells(myRow, 12) & vbCrLf & vbCrLf
myString = myString & "Extra Notes: " & Cells(myRow, 13) & vbCrLf & vbCrLf


    'Dim OutApp As Object
    'Dim OutMail As Object

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

    On Error GoTo Problem
   
   ' Change the mail address and subject in the macro before you run it.
''    With OutMail
''        .To = "dprogelhof@theapartmentgallery.com; fsturgill@theapartmentgallery.com"
''        .CC = "shicks@theapartmentgallery.com"
''        .BCC = ""
''        .Subject = "There is a issue with inspection: " & Cells(6, 3)
''        .Body = myString
''        .Attachments.Add ActiveWorkbook.FullName
''         You can add other files by uncommenting the following line.
''        .Attachments.Add ("C:\test.txt")
''         In place of the following statement, you can use ".Display" to
''         display the mail.
''        .Send
''    End With
   
    Set CD0_Mail = CreateObject("CDO.message")
    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
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "APTEMAILSERVER2.mail2.apartmentgallery.net"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    .Item(cdoSMTPAuthenticate) = 1
    .Item(cdoSendUserName) = "webmaster"
    .Item(cdoSendPassword) = "REnxFexr"
    .Update
    End With
   
    With CD0_Mail
        Set .Configuration = cdo_config
    End With
   
     With CD0_Mail
           .From= "mgmhicks@ee.com"
        .To = "dprogelhof@theapartmentgallery.com; fsturgill@theapartmentgallery.com"
        .CC = "shicks@theapartmentgallery.com"
        .BCC = ""
        .Subject = "There is a issue with inspection: " & Cells(6, 3)
        .TextBody = myString
        '.Attachments.Add ActiveWorkbook.FullName
        ' You can add other files by uncommenting the following line.
        '.Attachments.Add ("C:\test.txt")
        ' In place of the following statement, you can use ".Display" to
        ' display the mail.
        .Send
    End With
   
   
    'Set OutMail = Nothing
    'Set OutApp = Nothing
   
    Set CD0_Mail = Nothing
    Set cdo_config = Nothing      

   Exit Function
Problem:
    MsgBox Err.Description
    Exit Function
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question