Body of email client with cell information.

E

excellearner

New member
Joined
Nov 16, 2012
Messages
2
Reaction score
0
Points
0
Hey guys, im working right now on an email client that sends an email depending on a due date based on a cell. Im having difficulties though with retrieving a piece of information from a cell into the subject line (and eventually in the body of the email).
Im using Excel 2007 and Outlook as my email client and have gotten a hold of this code, but its giving me an the error: Compile Error Expected: end of statement.

Code: 
Sub Email()
Dim myOutlook As Object
Dim myMailItem As Object
Dim FName As String
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
FName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
With otlNewMail
.To = ""
.CC = ""
.Subject = "ActiveSheet.Range("B1") Homework "
'Here what im trying to do is make sure that if the word chemistry was in cell B1, the subject line would say: chemistry Homework
.Body = ""
.DeferredDeliveryTime = Range("A1")
.Send
End With
 
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
End Sub

Any help would be very much appreciated. Btw, this is my first post so i apologize up front for any mistake :/
 
Hey there,

Sorry, I just realized that this post hasn't been answered.

A few things... you're code is a bit mixed up with references and such. What you may want to do is download my Easy Outlook module from here: http://www.excelguru.ca/content.php?249-Easy-Outlook-Email-Integration

From there it's fairly simple:
Code: 
Public Sub EmailViaOutlook()
'Create the email object
    Dim oEmail As New clsOutlookEmail
    With oEmail
        'Add a recipient
        .AddToRecipient = "[EMAIL="To_someone@somedomain.com"][COLOR=#417394]To_someone@somedomain.com[/COLOR][/EMAIL]"
 
        'Set the subject
        .Subject = "ActiveSheet.Range("B1") & " Homework "

        'Add an attachment
        .AttachFile = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

        'Preview the email (or use .Send to send it)
        .Preview
    End With
    'Release the email object
    Set oEmail = Nothing
End Sub

Hope this helps,
 
Hey Ken,
First of all, thanks for taking the time to reply to my thread but im facing a different kind of problem right now. I have worked on the code little bit and am really struggling with one part.

Sub EmailThree()
Dim myOutlook As Object
Dim myMailItem As Object
Dim FName As String


If Range("B2") = Date Then
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
FName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
With otlNewMail
.To = "waseeeman@gmail.com"
.CC = ""
.Subject = Cells(2, 1) & " Homework"
.Body = "Hey this a reminder that you have a " & Cells(2, 5) & " " & Cells(2, 1) & " Homework due in " & Cells(2, 3) & " day(s). Dont forget this info when doing it: " & Cells(2, 4)
.Send
End With
End If

Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
ActiveWorkbook.Close
End Sub

This is the code that i programmed and what i want to do is to let the macro send only if the date in Cell B2 corresponds with the current date which is right now not working for whatever reason. I made sure that the cell reference in combination with the Date is the problem by replacing Range("B2") with Date and it worked perfectly. I was wondering whether i was doing anything wrong.
Btw, the cell B2 has 30.11.2012 formatted as a date in the Worksheet 1.

Thank you in advance,
 
You must log in or register to reply here.
Back
Top