• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help with getting the desired output

Hi,
Please help me fix this code, I have mentioned my requirements below in bold
Password to the code is pursuit


>>> use code - tags <<<
Code:
Sub esendtable()

Dim outlook As Object
Dim newEmail As Object
Dim xInspect As Object
Dim pageEditor As Object

Set outlook = CreateObject("Outlook.Application")
Set newEmail = outlook.CreateItem(0)

With newEmail
    .to = "fernandez@gmail.com"
    .CC = ""
    .BCC = ""
    .Subject = "Pursuits Status: " & Date
    .body = "Hi All," & Chr(10) & Chr(10) & "Mentioned below are the status of all the Pursuits which are currently in progress." & Chr(10) & Chr(10) & "For more details click here to access the Pursuit Tracker."
    .display
    '& vbCrLf & "Regards" & Chr(10) & "SMaaS Pursuit Team"
    '.body = str1 & table & str2

    Set xInspect = newEmail.GetInspector
    Set pageEditor = xInspect.WordEditor

    Sheet8.Range("A12:O20").Copy
''''' want to make this dynamic to select the entire table with xldown & xlup
   
'''''After the table want to add the Signature (Regards, Fernandez)
    pageEditor.Application.Selection.Start = Len(.body)
    pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
    pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
    .display
    '.Send
   
    Set pageEditor = Nothing
    Set xInspect = Nothing

End With

    Set newEmail = Nothing
    Set outlook = Nothing

End Sub
 

Attachments

  • Pursuits_Tracker - EMAIL USING MACRO.xlsm
    376.2 KB · Views: 4
Last edited by a moderator:
I want the body of the email to read in the below format:

Hi All,

Mentioned below are the status of all the Pursuits which are currently in progress.

For more details, click here to access the Pursuit Tracker.

StatusPursuit NameSMaaS
Pursuit Lead
TCV in Million USDSMaaS Value
(Revenues)
% SMaaS Revenues of TCVContract Duration - MonthsProposed SolutionCurrent StateSLA/Reporting
complexity
Solution Due DateOrals DatePlanned Transition Start DateWinning Probability (%)Comments
WIP7035959Amrit$31,070,089$292,9550.94%39 ALLEstimates Approved by SMaaS LeadMedium12-Jun-20 01-Sep-20
WIP695146Savita$52,765,155$67,4720.13%14SLA’s & Reporting; Process Set UpEstimates Approved by SMaaS LeadMedium12-Jun-20 01-Mar-21Most Likely
WIP7699596Amrit$55,344,186$174,0960.31%36SLA’s & Reporting; Process Set UpEstimates Approved by SMaaS LeadSimple29-May-20 15-Jun-20
WIP4078880Shambu$83,931,982$121,3720.14%60SLA’s & ReportingDeal/Solution ReviewSimple08-May-2001-Jun-2001-Jul-20Likely
WIP3290663Amrit$27,950,078$41,7300.15%36Process IntegrationEstimates Approved by SMaaS Lead 29-May-20 01-Jul-20
WIP4065346Amrit$87,984,977$595,2320.68%36 ALLEstimates Approved by SMaaS LeadComplex01-Jun-2001-Oct-20
WIP5284510Savita$37,112,349$110,0960.30%36 Estimates Approved by SMaaS Lead 30-Apr-20 Likely
WIP7639583Amrit$46,260,143$119,5530.26%12 ALLEstimates Approved by SMaaS Lead 10-Apr-20

Table 2

StatusPursuit NameSMaaS
Pursuit Lead
TCV in Million USDSMaaS Value
(Revenues)
% SMaaS Revenues of TCVContract Duration - MonthsProposed SolutionCurrent StateSLA/Reporting
complexity
Solution Due DateOrals DatePlanned Transition Start DateWinning Probability (%)Comments
WIP9711132Amrit$26,703,110$587,7432.20%60SLA’s & ReportingOrals Planned 20-Mar-2017-Jul-2001-Oct-20



Regards,
Fernandez
 
Hi, Fernandez. I don't think I'll look over your whole program trying to find problems, not without more hints. You say want to know how to fix the program: What, exactly, is the problem? What happens when you run this program that makes you think it's not right?
 
Hi Bob,
Thank you for taking time to help me.

>>> 2nd time - use code - tags <<<
Code:
    Sheet8.Range("A12:O20").Copy
''''' want to make this dynamic to select the entire table with xldown & xlup (table that needs to be copied is on sheet 8 named as Daily Report. I tried making it dynamic by adding the below code, however it gives me an error, need help with this.

Dim count_row, count_col As Integer
'count_row = WorksheetFunction.CountA(Range(A12, Range(A12).End(xlDown)))
'count_col = WorksheetFunction.CountA(Range(A12, Range(A12).End(xlToRight)))
 
'''''After the table want to add the Signature (Regards, Fernandez), which is not coming up atall.
 
Last edited by a moderator:
Hi, Fernandez. I don't think I'll look over your whole program trying to find problems, not without more hints. You say want to know how to fix the program: What, exactly, is the problem? What happens when you run this program that makes you think it's not right?

Hi Bob, were you able to look into this for me, do you need some other information from me.
 
Attached herewith the email I am trying to generate using the above code. (Body of the email + 3 tables )

You will find the tables in the attached excel workbook, sheet 8 renamed as "Daily Report".
1st Table : filter on column i (Current State) to Orals Planned
2nd Table: Estimation submitted for Approval & RFP Study & Estimation WIP
3rd Table: Estimation Reviewed & Approved & Estimates Approved by SMaaS Lead & Deal/Solution Review & Estimates Submitted

Then Signature as (regards,Raymond)

Sorry couldnt explain in 10 words.
 

Attachments

  • Email.PNG
    Email.PNG
    81.7 KB · Views: 4
Last edited:
Back
Top