1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2007 -How do I get cell data to populate email?

Discussion in 'Business Applications' started by desantisj, Nov 19, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. desantisj

    desantisj Thread Starter

    Joined:
    Nov 18, 2009
    Messages:
    4
    Hi:
    I have a couple of questions regarding the below code and the attached spreadsheet. What do I have to do to make this macro execute at the time indicated in col m of the spreadsheet? The dates are going to be different for each row.
    Also how do I get cell data [a4] [h4] [g4] [m4] [n4] to populate from the worksheet into the email?

    Sub Mail_small_Text_Outlook()
    'Working in Office 2000-2007
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    strbody = "Andean Funding Closing Document has not been recieved" & vbNewLine & vbNewLine & _
    "Andean Tracking Number: [a4]" & vbNewLine & _
    "Requested Amount: [h4]" & vbNewLine & _
    "Case Number: [g4]" & vbNewLine & _
    "Closure Document Due NLT Date: [m4]" & vbNewLine & _
    "Staff Coordinator: [n4]" & vbNewLine & _
    "Please contact OGL immediately to correct this situation" & vbNewLine & vbNewLine & vbNewLine & vbNewLine & vbNewLine & _
    "Judy De Santis" & vbNewLine & _
    "Office of Global Enforcement" & vbNewLine & _
    "Latin America Caribbean Section" & vbNewLine & _
    "Office: 202-307-4609" & vbNewLine & _
    "Cell: 202-345-9257" & vbNewLine & _
    "Fax: 202-307-8459" & vbNewLine & _
    "E-mail: [email protected]"

    On Error Resume Next
    With OutMail
    .To = [email protected]
    .CC = ""
    .BCC = ""
    .Subject = "Andean Funding Closing Document Required"
    .Body = strbody
    'You can add a file like this
    '.Attachments.Add ("C:\test.txt")
    .Send 'or use .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub
     

    Attached Files:

  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    OnTime method (never tried it myself, but I hear it's very processor-hungry -- some kind of ?appointment? in (e.g.) Outlook might be preferable, Zack B would be your man for that).

    I'm guessing from the style (e.g. "'Working in Office 2000-2007") that's Ron de Bruin code. See where you have a section such as:

    strbody = "Andean Funding Closing Document has not been recieved" & vbNewLine & vbNewLine & _
    "Andean Tracking Number: [a4]" & vbNewLine & _


    ? Try:

    strbody = "Andean Funding Closing Document has not been recieved" & vbNewLine & vbNewLine & _
    "Andean Tracking Number: "&Range("A4")& vbNewLine & _


    instead. HTH
     
  3. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Update.

    I pasted the code into a module. Layout-wise, it gave me this line:

    "Andean Tracking Number: [a4]" & vbNewLine & _

    which I edited to:

    "Andean Tracking Number: " & Range("A4") & vbNewLine & _

    Then I ran it & got this within the email text:

    Andean Funding Closing Document has not been recieved

    Andean Tracking Number: Here is the test text I typed in cell A4
    Requested Amount: [h4]


    Do you follow?

    WARNING: you must change the email line (.To =), the email address has to be in quotemarks or it'll crash.

    (@OBP: oops :eek: -- :D )
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    And then remove the email address and phone numbers from your post please.
     
  5. desantisj

    desantisj Thread Starter

    Joined:
    Nov 18, 2009
    Messages:
    4
    Appreciate the help. It appears to work. Being new to Excel 2007 and vb code, my head is spinning. Thanks for taking the time to assist.

    desantisj
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/878704

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice