Posts by elsuji

elsuji · Sep 16th 2021

Dear Team,

Need your help to resolve my problem

elsuji · Sep 13th 2021

Dear Roy,

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

Code

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 & " " _
& "Customer Name : " & cell.Offset(, -65).Value & " " _
& "Location : " & cell.Offset(, -59).Value & " " _
& "Contact No : " & cell.Offset(, -60).Value & " " _
& "Support required : " & cell.Offset(, 1).Value
End If
sbody = "" & "Dear Team, " _
& "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

elsuji · Sep 12th 2021

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

Code

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 = " " & "" & "Dear Service Team, " _
& "Please take care of the below list, " _
& "Customer Name : " & cell.Offset(, -65).Value & " " _
& "Location : " & cell.Offset(, -59).Value & " " _
& "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

elsuji · Sep 12th 2021

Is there any other way for this. Pls help me

elsuji · Sep 11th 2021

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

Code

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 = " " & "" & "Dear Service Team, " _
& "Please take care of the below list, " _
& 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

elsuji · Sep 8th 2021

Thanks for your reply

elsuji · Sep 8th 2021

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

elsuji · Jul 29th 2021

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

elsuji · Jul 27th 2021

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

elsuji · Jun 11th 2021

Dear Roy,

Can you please reply me to complete my work

elsuji · Jun 5th 2021

you can add the date on sheet"WELCOME" A10

elsuji · Jun 5th 2021

Dear Roy,

I have attached my file here for your reference.

the code is in module 9

elsuji · Jun 5th 2021

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"

elsuji · Jun 5th 2021

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

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

elsuji · May 29th 2021

Dear Roy,

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

Can you please clear it.

elsuji · May 29th 2021

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

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

elsuji · May 28th 2021

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

elsuji · Feb 3rd 2021

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?

elsuji · Nov 29th 2020

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.

elsuji · Nov 29th 2020

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.

Posts by elsuji

Mail body table format from sheet

Mail body table format from sheet

Mail body table format from sheet

Mail body table format from sheet

Mail body table format from sheet

consider last row if there is any blank cell in between

consider last row if there is any blank cell in between

Need help in COUNTIF formula

Need help in COUNTIF formula

Get Week no of the current month

Get Week no of the current month

Get Week no of the current month

Get Week no of the current month

Get Week no of the current month

Need help in COUNTIF formula

Need help in COUNTIF formula

Need help in COUNTIF formula

Need help to create cycle chart

Border for last row of all page brake

Border for last row of all page brake