Results 1 to 8 of 8

Thread: identify a range of data to send in an email
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2017
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default identify a range of data to send in an email

    This is the start of the code:
    Sub Button1_Click()
    Dim CDO_Mail As Object
    Dim CDO_Config As Object
    Dim SMTP_Config As Variant
    Dim strSubject As String
    Dim strFrom As String
    Dim strTo As String
    Dim strCc As String
    Dim strBcc As String
    Dim strBody As String


    strSubject = "MHR Request"
    strFrom = "tbrynard@harmonyfoundationinc.com"
    strTo = "tbrynard@harmonyfoundationinc.com"
    strCc = ""
    strBcc = ""
    strBody = ???

    I'm stuck on how to display a range of data from the sheet in the body of the email??

    Thanks

  2. #2
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,050
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: identify a range of data to send in an email


  3. #3
    New Member
    Join Date
    Sep 2017
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: identify a range of data to send in an email

    I looked at that document but am not sure what needs to go there. I apologize don't really know VBA that well and found that code. The rest works well, only challenge is to copy the range of cells from the worksheet into the email.

    Thank you

  4. #4
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,050
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: identify a range of data to send in an email

    If you don't know what your range or other data for the email is, I sure don't.

    Snippet example
    Ron's code:
    Code:
    .To = "ron@debruin.nl"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .HTMLBody = RangetoHTML(rng)
    Modified examples:
    Code:
            .To = Range("A2")
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line: " &  Range("B2")
            '.HTMLBody = "Hello World! & vbcrlf & RangetoHTML(ActiveSheet.UsedRange)
            .HTMLBody = "Hello World! & vbcrlf & RangetoHTML(Range("A1:E21"))
    Last edited by Kenneth Hobson; Sep 12th, 2019 at 12:45 PM.

  5. #5
    New Member
    Join Date
    Sep 2017
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: identify a range of data to send in an email

    I apologize if I seem ignorant, this is the code that I found that is working until I get to the body of the email
    Sub Button1_Click()
    Dim CDO_Mail As Object
    Dim CDO_Config As Object
    Dim SMTP_Config As Variant
    Dim strSubject As String
    Dim strFrom As String
    Dim strTo As String
    Dim strCc As String
    Dim strBcc As String


    strSubject = "MHR Request"
    strFrom = "tbrynard@harmonyfoundationinc.com"
    strTo = "tbrynard@harmonyfoundationinc.com"
    strCc = ""
    strBcc = ""

    your example is using .htmlbody and I'm not sure where to put that

    the sheet that contains my data is Sheet1 (which I named Form) and the data range is A1:D19, again am a novice and not sure where to add what in the code above.

    I appreciate your help and patience!

  6. #6
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,050
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: identify a range of data to send in an email

    Please paste code between code tags. Click the # icon on reply bar to insert the tags.

    Your code is partial and does not assign the values to the OutLook or CDO fields. You don't really need those variables. Just do the work in the Outlook field values. Some use a variable for the .Body or .htmlBody since it may have lots of string concatenation but that can be done in the field too. You don't need to assign "" to fields. Just set the values for the fields needed. This is based on Ron's partial code and yours. Obviously, you need to add his RangeToHTML() routine.
    Code:
    'snip...
    
    strSubject = "MHR Request"
    strFrom = "tbrynard@harmonyfoundationinc.com"
    strTo = "tbrynard@harmonyfoundationinc.com"
    strCc = ""
    strBcc = ""
    
    '.snip......
    
    '.SentOnBehalfOf = strFrom 'Not needed normally
    .To = strTo
    .CC = strCC
    .BCC =strBCC
    .Subject = strSubject
    '.HTMLBody = "Hello World! & vbcrlf & RangetoHTML(ActiveSheet.UsedRange)
     .HTMLBody = RangetoHTML(WorkSheets("Form").Range(A1:D19"))
    'or
    '.HTMLBody = RangetoHTML(WorkSheets(1).Range(A1:D19"))
    'or
    '.HTMLBody = RangetoHTML(Sheet1.Range(A1:D19")) 'Sheet1 is the sheet's codename, which may not be the tab's name, Form.
    
    'snip......
    It looks like you are using CDO rather than the Outlook object? I would have to check to see if it allows a SendOnBehalfOf field. Again, without the full code, I don't really know what all you have going on.

    CDO does have .Body and .htmlBody as Outlook does. Outlook also has a WordEditor method to fill the body of the email. With Outlook you can use 1 of 3 body fill methods. CDO, you can use 1 of 2 methods.
    Last edited by Kenneth Hobson; Sep 12th, 2019 at 01:33 PM.

  7. #7
    New Member
    Join Date
    Sep 2017
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: identify a range of data to send in an email

    Here's the full code I'm using the body is the only issue I'm having if I take that out the rest works perfectly. Again thank you!

    Dim CDO_Mail As Object
    Dim CDO_Config As Object
    Dim SMTP_Config As Variant
    Dim strSubject As String
    Dim strFrom As String
    Dim strTo As String
    Dim strCc As String
    Dim strBcc As String
    Dim strHTMLBody As String
    Dim strRangetoHTML As String


    strSubject = "MHR Request"
    strFrom = "tbrynard@harmonyfoundationinc.com"
    strTo = "tbrynard@harmonyfoundationinc.com"
    strCc = ""
    strBcc = ""
    .HTMLBody = RangetoHTML(Worksheets(1).Range("A1:D19"))


    Set CDO_Mail = CreateObject("CDO.Message")
    On Error GoTo Error_Handling


    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") = "smtp.outlook.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "tbrynard@harmonyfoundationinc.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxxxxxxxxxx"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    .Update
    End With


    With CDO_Mail
    Set .Configuration = CDO_Config
    End With


    CDO_Mail.Subject = strSubject
    CDO_Mail.From = strFrom
    CDO_Mail.To = strTo
    CDO_Mail.TextBody = strBody
    CDO_Mail.CC = strCc
    CDO_Mail.BCC = strBcc
    CDO_Mail.Send


    Error_Handling:
    If Err.Description <> "" Then MsgBox Err.Description


    End Sub

  8. #8
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,050
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: identify a range of data to send in an email

    .TextBody in CDO is the same as .Body in Outlook. Replace it as I explained with .htmlBody for either CDO or Outlook. The dot, period, ".", is used when people use With SomeObjectName like CDO_Mail.

    If still a problem please add the code tags and post back. I will fix it as explained. Code tags keep your structure (indents) so it is easier to read what is going on.

    Here is some CDO code that I have used for Gmail.

    Code:
    Sub Test_Gmail()
      Gmail "ken@gmail.com", "ken", "Hello World!", _
        "This is a test using CDO to send Gmail with an attachement.", _
        "khobson@somewhere.org", "uknowwho@nowwhere.com", _
        "x:\test\test.xlsm"
    End Sub
    
    ' http://www.blueclaw-db.com/access_email_gmail.htm
    ' http://msdn.microsoft.com/en-us/library/ms872547%28EXCHG.65%29.aspx
    ' Add CDO reference for early binding method
    '  Tools > References > Microsoft CDO for Windows 2000 Library
    '    c:\windows\system32\cdosys.dll
    ' http://www.rondebruin.nl/cdo.htm  'Other cdo tips for cdo to Outlook from Excel
    
    'CDO to gmail requires lowering your security:
    'https://myaccount.google.com/security#connectedapps
    'at the end set, Allow less secure apps: ON
    
    Function Gmail(sendUsername As String, sendPassword As String, subject As String, _
      textBody As String, sendTo As String, sendFrom As String, _
      Optional sAttachment As String = "")
      
      Dim cdomsg As New CDO.Message  'early binding method
      'set cdomsg=new CDO.Message 'early binding only
      'Dim cdomsg As Object 'late binding method
      Set cdomsg = CreateObject("CDO.message")  'late binding method or early binding
     
      With cdomsg.Configuration.Fields
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'NTLM method
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 25  '25 or 587
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = sendUsername
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = sendPassword
        .Update
      End With
      ' build email parts
      With cdomsg
        .To = sendTo
        .From = sendFrom
        .subject = subject
        .textBody = textBody
        '.BCC
        '.CC
        '.ReplyTo = sendFrom
        '.HTMLBody
        '.HTMLBodyPart
        If Dir(sAttachment) = "" Then sAttachment = ""
        If sAttachment <> "" Then .AddAttachment (sAttachment)
        .Send
      End With
      Set cdomsg = Nothing
    End Function
    Last edited by Kenneth Hobson; Sep 12th, 2019 at 03:45 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •