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)
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 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
