cell references in body of email macro

banneduser123

Banned - Rules violations
Joined
Mar 29, 2006
Messages
181
Hi, i'm pretty new to coding, so would appreciate any help - i've included the code for an email macro i've kinda put together.
Would appreciate any suggestions on how to make this more efficient or better code.

My issue
I want to add a line in the body of this email. This line would be composed of Text as well as cell references.

For your assistance

Cell references - F3, H3
Worksheet Name - Pre Pack



Ultimately, this particular line in the body of the email would have the below final result, where ‘John’ and ‘000-000-0000’ are the cell references. the red font is Text


Notary: John : 000-000-0000



hope this was helpful/clear...please let me know if you need better clarification on anything



VBA Code:
Sub CD_Mod()

    
    Dim OApp As Object, OMail As Object, signature As String

    Set OApp = CreateObject("Outlook.Application")
    Set OMail = OApp.CreateItem(0)

    Dim SrcSheet As Excel.Worksheet
    Set SrcSheet = Sheets("Mortgage Notes")

With OMail
.Display
End With

signature = OMail.Body

With OMail
        .To = SrcSheet.Range("A56").Text
        .CC = SrcSheet.Range("B56").Text
        .Subject = SrcSheet.Range("C56").Text
        .HTMLBody = "<font style=""font-family: Calibri; font-size: 11pt;""/font>" & "Hi, " & "<br>" & "<br>" & "A modification was posted on this order." & "<br>" & "Please provide an updated Final CD for closing." & "</p>" & .HTMLBody & vbNewLine & signature
        .Display
        '.Send
    End With



Set OMail = Nothing
Set OApp = Nothing
End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
so i figured out how to include the cell references in the body,
but when it creates the email, it loses the formatting from the cell...

is it possible to keep the formatting from the cell?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,214,591
Messages
6,120,424
Members
448,961
Latest member
nzskater

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top