Dear Team,
Need your help to resolve my problem
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
Display More
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
Display More
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,
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
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
Display More
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 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?
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?
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.