Posts by elsuji

    Dear Roy,


    I changed the body format from table to list and my new code is


    My mail body looks


    Dear Team,

    Please take care of the below customers problem,

    Customer Name : M/S ACC Ltd
    Location : Magadi Road
    Contact No : 8884433993
    Support required : Customer has requested to share the Spares catalogue/Electrical Drawing of M1CR batching plant.

    Customer Name : M/s Vavedha Infra
    Location : office address
    Contact No : 23781623
    Support required : support required

    Customer Name : M/s Ramalingm Construction
    Location : office address
    Contact No : 23781623
    Support required : support required

    Customer Name : M/s ZERCON RMC
    Location : Bangalore
    Contact No : 8884433993
    Support required : Customer has requested to share the Spares catalogue/Electrical Drawing of M1CR batching plant.




    Now the problem is the body content is updating only for service.


    But I want to generate separate mail for another department also.


    Can you please help me trigger multiple mails for different branch

    Dear Ray,


    I changed the code and now my mail body is like this


    Dear Service Team,

    Please take care of the below list,

    Customer Name : M/s ZERCON RMC
    Location : Bangalore
    Support required : Customer has requested to share the Spares catalogue/Electrical Drawing of M1CR batching plant.


    Code is


    But it is updating only one row. I want to update all the row details where the service is in BP column.


    Can you help me

    Dear Team,


    I created code for sending mail based on value matched. I want to insert the data to the mail body in table format from the sheet.


    In column BP if it is in service then the contents to be copied from columns C, I & BQ


    and the body format is


    Dear Service Team,

    Please take care of the below list,



    Customer Name Office address Support required
    M/S ACC Ltd Magadi Road The customer has requested to share the Spares catalogue/Electrical Drawing of the M1CR batching plant.
    M/s Vavedha Infra office address support required
    M/s Ramalingm Construction office address support required
    M/s ZIRCON RMC Bangalore The customer has requested to share the Spares catalogue/Electrical Drawing of the M1CR batching plant.



    and another mail to be triggered for the Spares team


    Dear Spares Team,

    Please take care of the below list,


    Customer Name Office address Support required
    M/s Panchami Concrete office address support required
    M/s HI Tech RMC office address support required
    M/s CCCL Infra office address support required



    And also another mail to be triggered for Units Team


    Dear Units Team,

    Please take care of the below list,


    Customer Name Office address Support required
    M/S Shree Concrete Magadi Road The customer has requested to share the Spares catalogue/Electrical Drawing of the M1CR batching plant.
    M/s Tulasi concrete office address support required


    My code is




    Can anyone help me with how to do the above y requirements? My file is attached here for your kind reference

    Files

    • Book1.xlsm

      (38.73 kB, downloaded 8 times, last: )

    Dear Team,


    In column A, if there is any blank cell, I won't use the last row till the blank cell. Not the entire column last used cell.


    For example,


    My range starts from A9. My values are entered A9 to A40. But If suppose A30 is a blank cell. So my last row should work till A29. Not till A40. Can anyone help me how to do this

    Dear Mr.Roy,


    First of all sorry for the late reply. I was busy with some other work. So I am not able to reply to you.


    I checked your above file and created the table format. It was working when er add new data on the sheet and counting the values.


    But I am adding the values through the user form entry. Whenever I am trying to add values from the user form on the table, the blank row is created after the table.


    I attached my file here for your reference.


    I am adding the values on the "Weekly Visit report" sheet and I did the formula to the 'Weekly Visit report'!AX1.


    Can you please chek my attached file and reply me back

    Files

    • CRM.xlsm

      (118.62 kB, downloaded 14 times, last: )

    I am not sure about DefinedDate value. I get it from net.


    i tried WorksheetFunction.WeekNum(cDate(DefinedDate), vbMonday).I changed my system date to 18.06.2021. Then also it is updating 1st week only.


    Also help me to update "2nd" & "3rd" & "4th"

    Dear Team,


    I am sending outlook mail through VBA every week. I want to update the week no (1st week or 2nd week or 3rd week or 4th week or 5th week ) on my mail body.


    My code is


    Code
    1. sMessage = "<br>" & "<font size=""3"" face=""Cambria"" >" & "Dear Sir, <br><br>" _
    2. & " Herewith I am forwarding the " & "<b> CRM Weekly Preplanner </b>" & " report for the " _
    3. & WorksheetFunction.WeekNum(DefinedDate, vbMonday) & "st week of " & Format(Date, "mmm") & " " & Year(Date) & ".<br><br>" _
    4. & " Kindly find the attachment. "

    when I run the above code my output is


    Dear Sir,

    Herewith I am forwarding the CRM Weekly Preplanner report for the 1st week of Jun 2021.

    Kindly find the attachment.


    I checked to change my system date manually. It is updating 1st week only.


    Can anyone help me with how to update as per my requirement

    Dear Roy,


    Thanks for your reply.


    I tried the following formula =SUM(1/COUNTIF(B2:B28, B2:B28)) Finish by pressing CTRL + SHIFT + ENTER it is working properly.


    But the problem is the last row. If I add values on B29 it is not counting. Because the last row defined B28 on my formula.


    So I tried using VBA code. My VBA code is


    Code
    1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    2. Dim ws As Worksheet, lRow As Long, DifValue As String
    3. Set ws = ThisWorkbook.Sheets("Weekly Visit report")
    4. With ws
    5. lRow = .Range("B" & .Rows.Count).End(xlUp).Row
    6. .Range("AR3").Value = Application.WorksheetFunction.Sum(1 / Application.WorksheetFunction.CountIfs(CHData.Range("B2:B" & lRow), CHData.Range("B2:B" & lRow)))
    7. End With
    8. End Sub

    But it is not working.


    Can you please help me to complete this

    Dear Team,


    I need the formula to count how many clients are entered into my column.


    For example, on my column B2, I am having the following list



    I want my result to be updated on 3. because I am having three clients (Client A, Client B, Client C).


    If I add one more client name like Client D then my result should update 4 automatically.


    Can anyone help me with how to do this?

    client A
    Client A
    Client B
    Client C
    Client A
    Client B

    Dear Team,


    I want to create a cycle chart (graph) for my plant operation. I have drawn the graph manually as per my data. But I want this graph should create automatically once I entered the values.


    I attached my file here.


    I want the graph should create automatically as shown in the sheet "Plant Cycle Chart". In sheet "Graph" I have shown how the data will be entered.


    I don't know how to create this graph format on excel. Can anyone please help me how to do this?