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

rahulin

New Member
Joined
Aug 8, 2010
Messages
4
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

 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

rahulin

New Member
Joined
Aug 8, 2010
Messages
4
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.

9fnewqwj




Uploaded with ImageShack.us


regards,

Rahul
 

Watch MrExcel Video

Forum statistics

Threads
1,132,873
Messages
5,655,737
Members
418,234
Latest member
jdorfma

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
Top