Using Excel 2007 to Prepare an Email in Outlook 2007 in a customized format.

rahulin

New Member
Joined
Aug 8, 2010
Messages
3
Hi

I am a new user to this board and I must say I am really thankful to what all help I've been able to receive from this website in order to learn and understand how Excel Macro's work.

I am trying to create a Macro, which can actually retrieve data from Hyperion Essbase system for my retrieve template (already completed this Macro :)).

Second Part:

Now I am working on creating this Macro to create an email using Outlook which will have the message as below:

-------------------------------------------------------------------------
Hi,

Please find the link to Jul-10 file for 06-08-10 for Group 1 :

link to the file:

"D:\Documents and Settings\admin\Desktop\New.xlsx"


Total = $ Debit 409k

1. Random 1 = $ Credit 50k (Need to check with Dept)
2. Random 4 = $ Credit 60k (Genuine)
a. Random 2 = $ Credit 50k (Cause Known)
b. Random 3 = $ Credit 10k (Unsure)
3. Random 5 = $ Nil
4. Random 6 = $ Credit 100k (Expected)
5. Random 7 = $ Credit 1k (Immaterial)
6. Random 8 = $ Debit 10k (Investigation pending)
7. Random 9 = $ Debit 30k (Investigation pending)
8. Random 10 = $ Debit 550k (Investigation pending)
9. Random 11 = $ Debit 30k (Investigation pending)


Thanks and Kind regards,

Signature [Auto signature should come up]

-------------------------------------------------------------------------

The format is really critical, it has to have the format as stated above only and no change.



The numbers for each item gets populated from Column 'H' in the attached excel file, the Debits needs to appear in Red (Bold) and Credits in Green, the comment section in the attached file (Column BX) comes after each line in brackets in Black (Bold color only) as shown on the above format.

I am struggling with the formatting section when trying to code, any help would be useful.

Thanks and Kind regards

Rahul


Code:

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim Today As Date
Today = Date
Dim r As Integer, x As Double
For r = 2 To 4 'data in rows 2-4
' Get the email address
Email = Cells(r, 2)

' Message subject
Subj = Cells(r, 3)

' Compose the message
Msg = ""
Msg = Msg & "Please find the link to " & Cells(r, 4) & Format(Today, "DD-MMM-YY") & " " & vbCrLf
'ActiveWorkbook.SaveAs Filename:="My_File" & Format(Today, "YYYY_MM_DD")
'Msg = Msg & "FOF variance" & Cells(r, 4) - EEM - Audience = $ Credit 25kMsg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf



' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")

' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

' Wait two seconds before sending keystrokes
'Application.Wait (Now + TimeValue("0:00:02"))
'Application.SendKeys "%s"
Next r
End Sub

 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

rahulin

New Member
Joined
Aug 8, 2010
Messages
3
Hi

I am a new user to this board and I must say I am really thankful to what all help I've been able to receive from this website in order to learn and understand how Excel Macro's work.

I am trying to create a Macro, which can actually retrieve data from Hyperion Essbase system for my retrieve template (already completed this Macro :)).

Second Part:

Now I am working on creating this Macro to create an email using Outlook which will have the message as below:

-------------------------------------------------------------------------
Hi,

Please find the link to Jul-10 file for 06-08-10 for Group 1 :

link to the file:

"D:\Documents and Settings\admin\Desktop\New.xlsx"


Total = $ Debit 409k

1. Random 1 = $ Credit 50k (Need to check with Dept)
2. Random 4 = $ Credit 60k (Genuine)
a. Random 2 = $ Credit 50k (Cause Known)
b. Random 3 = $ Credit 10k (Unsure)
3. Random 5 = $ Nil
4. Random 6 = $ Credit 100k (Expected)
5. Random 7 = $ Credit 1k (Immaterial)
6. Random 8 = $ Debit 10k (Investigation pending)
7. Random 9 = $ Debit 30k (Investigation pending)
8. Random 10 = $ Debit 550k (Investigation pending)
9. Random 11 = $ Debit 30k (Investigation pending)


Thanks and Kind regards,

Signature [Auto signature should come up]

-------------------------------------------------------------------------

The format is really critical, it has to have the format as stated above only and no change.



The numbers for each item gets populated from Column 'H' in the attached excel file, the Debits needs to appear in Red (Bold) and Credits in Green, the comment section in the attached file (Column BX) comes after each line in brackets in Black (Bold color only) as shown on the above format.

I am struggling with the formatting section when trying to code, any help would be useful.

Thanks and Kind regards

Rahul


Code:

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim Today As Date
Today = Date
Dim r As Integer, x As Double
For r = 2 To 4 'data in rows 2-4
' Get the email address
Email = Cells(r, 2)

' Message subject
Subj = Cells(r, 3)

' Compose the message
Msg = ""
Msg = Msg & "Please find the link to " & Cells(r, 4) & Format(Today, "DD-MMM-YY") & " " & vbCrLf
'ActiveWorkbook.SaveAs Filename:="My_File" & Format(Today, "YYYY_MM_DD")
'Msg = Msg & "FOF variance" & Cells(r, 4) - EEM - Audience = $ Credit 25kMsg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf



' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")

' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

' Wait two seconds before sending keystrokes
'Application.Wait (Now + TimeValue("0:00:02"))
'Application.SendKeys "%s"
Next r
End Sub

I dont know how to attach the file here, while I discover that I've attached the screen shot of my file.





Uploaded with ImageShack.us


regards,

Rahul
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,020
Messages
5,508,849
Members
408,697
Latest member
Jay_Sks

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top