Posts by elsuji

Dear Team,

Need your help to resolve my problem

Dear Roy,

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

Sub Grievances_Mail()
    Dim olApp As Object
    Dim olMail As Object
    Dim olInsp As Object
    Dim wdDoc As Object
    Dim oRng As Object
    Dim sPath As String
    Dim sMessage As String
    Dim sbody As String
    Dim sFile As String
    Dim cell As Range
    Dim sBCC As String
    Dim sh As Worksheet
    Dim rngA As Range
   
    
     Set sh = ThisWorkbook.Sheets("Weekly Visit report")
        For Each cell In sh.Columns("BP").Cells.SpecialCells(xlCellTypeConstants)
            If cell.Value Like "Service" Then
                sMessage = sMessage & "<br><br>" _
                & "Customer Name : " & cell.Offset(, -65).Value & "<br>" _
                & "Location : " & cell.Offset(, -59).Value & "<br>" _
                & "Contact No : " & cell.Offset(, -60).Value & "<br>" _
                & "Support required : " & cell.Offset(, 1).Value
            End If
            sbody = "<font size=""3"" face=""Cambria"" color=""Blue"">" & "Dear Team, <br><br>" _
                & "Please take care of the below customers problem," & sMessage
        Next cell
        



 
    Set olApp = CreateObject("Outlook.Application")


 
    On Error Resume Next
    Set olMail = olApp.CreateItem(0)
        With olMail
'            .BCC = sBCC
            .Subject = "GREETINGS FROM SCHWING STETTER!!!"
            .HTMLBody = sbody
    Set olInsp = .GetInspector
    Set wdDoc = olInsp.WordEditor 'access the message body for editing
            .Display 'required to edit message body
        End With

    Set olApp = Nothing
    Set olMail = Nothing
    Set olInsp = Nothing
    Set wdDoc = Nothing
    Set oRng = Nothing
 
End Sub

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

Sub Grievances_Mail()
    Dim olApp As Object
    Dim olMail As Object
    Dim olInsp As Object
    Dim wdDoc As Object
    Dim oRng As Object
    Dim sPath As String
    Dim sMessage As String
    Dim sFile As String
    Dim cell As Range
    Dim sBCC As String
    Dim sh As Worksheet
    
    
    Set sh = ThisWorkbook.Sheets("Weekly Visit report")
        For Each cell In sh.Columns("BP").Cells.SpecialCells(xlCellTypeConstants)
            If cell.Value Like "Service" Then
                sMessage = "<br>" & "<font size=""3"" face=""Cambria"" color=""Blue"">" & "Dear Service Team, <br><br>" _
                & "Please take care of the below list, <br><br>" _
                & "Customer Name : " & cell.Offset(, -65).Value & "<br>" _
                & "Location : " & cell.Offset(, -59).Value & "<br>" _
                & "Support required : " & cell.Offset(, 1).Value
            End If
        Next cell
 
    Set olApp = CreateObject("Outlook.Application")


 
    On Error Resume Next
    Set olMail = olApp.CreateItem(0)
        With olMail
'            .BCC = sBCC
            .Subject = "GREETINGS FROM SCHWING STETTER!!!"
            .HTMLBody = sMessage
    Set olInsp = .GetInspector
    Set wdDoc = olInsp.WordEditor 'access the message body for editing
            .Display 'required to edit message body
        End With

    Set olApp = Nothing
    Set olMail = Nothing
    Set olInsp = Nothing
    Set wdDoc = Nothing
    Set oRng = Nothing
 
End Sub

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

Is there any other way for this. Pls 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,

and another mail to be triggered for the Spares team

Dear Spares Team,

Please take care of the below list,

And also another mail to be triggered for Units Team

Dear Units Team,

Please take care of the below list,

My code is

Sub Grievances_Mail()
    Dim olApp As Object
    Dim olMail As Object
    Dim olInsp As Object
    Dim wdDoc As Object
    Dim oRng As Object
    Dim sPath As String
    Dim sMessage As String
    Dim sFile As String
    Dim cell As Range
    Dim sBCC As String
    Dim sh As Worksheet
    
    
    Set sh = ThisWorkbook.Sheets("Weekly Visit report")
        For Each cell In sh.Columns("BP").Cells.SpecialCells(xlCellTypeConstants)
            If cell.Value Like "Service" Then
                sMessage = "<br>" & "<font size=""3"" face=""Cambria"" color=""Blue"">" & "Dear Service Team, <br><br>" _
                & "Please take care of the below list, <br><br>" _
                & cell.Offset(, -65).Value & "  " & cell.Offset(, -59).Value & "  " & cell.Offset(, 1).Value
            End If
        Next cell
 
    Set olApp = CreateObject("Outlook.Application")


 
    On Error Resume Next
    Set olMail = olApp.CreateItem(0)
        With olMail
            .BCC = sBCC
            .Subject = "GREETINGS FROM SCHWING STETTER!!!"
            .HTMLBody = sMessage
    Set olInsp = .GetInspector
    Set wdDoc = olInsp.WordEditor 'access the message body for editing
            .Display 'required to edit message body
        End With

    Set olApp = Nothing
    Set olMail = Nothing
    Set olInsp = Nothing
    Set wdDoc = Nothing
    Set oRng = Nothing
 
End Sub

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

Thanks for your reply

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 Roy,

I checked your code. But when I am trying to add new from the user form I am getting an error

and if i press debug it heighlited on .Cells(oNewRw, 2) = Me.TextBox20.Value

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

Dear Roy,

Can you please reply me to complete my work

you can add the date on sheet"WELCOME" A10

Dear Roy,

I have attached my file here for your reference.

the code is in module 9

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

sMessage = "<br>" & "<font size=""3"" face=""Cambria"" >" & "Dear Sir, <br><br>" _
 & " Herewith I am forwarding the " & "<b> CRM Weekly Preplanner </b>" & " report for the  " _
 & WorksheetFunction.WeekNum(DefinedDate, vbMonday) & "st week of " & Format(Date, "mmm") & " " & Year(Date) & ".<br><br>" _
& " 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,

I am not clear on it. I have attached my sample workbook.

Can you please clear it.

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim ws As Worksheet, lRow As Long, DifValue As String
 Set ws = ThisWorkbook.Sheets("Weekly Visit report")
 With ws
 lRow = .Range("B" & .Rows.Count).End(xlUp).Row

.Range("AR3").Value = Application.WorksheetFunction.Sum(1 / Application.WorksheetFunction.CountIfs(CHData.Range("B2:B" & lRow), CHData.Range("B2:B" & lRow)))

 End With
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?

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?

Dear Roy,

Pls find the attached sample pdf file. I want to add border as per shown the pdf file on the page where reading are updated.

Dear roy,

I tried your code. But the border is not updating.

I attached new file with your code. Can you please look and correct me.