Solved

Sending email using cdo_message

Posted on 2013-06-20
3
441 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 500 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

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…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 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