Excel Macro for Email to be send with filter data and personalised subject

[bmkela2017]

Hello All,
I have a Excel file containing the data about the pending Insurance claims to be settled. this is for many insurance agents throughout the country and we need to send them their pending claims as email and letters as well.

The data would be like in the following format
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee Code[/TD]
[TD]Name of the Employee[/TD]
[TD]Name of customer[/TD]
[TD]policy number[/TD]
[TD]Expiry Date[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]ABC[/TD]
[TD]Mr. XYZ[/TD]
[TD]123456[/TD]
[TD]10-03-2018[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]BCD[/TD]
[TD]Mr. AHD[/TD]
[TD]124578[/TD]
[TD]09-03-2018[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]CDE[/TD]
[TD]Mr. EFG[/TD]
[TD]121212[/TD]
[TD]01-01-2018[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]ABC[/TD]
[TD]Mr. EFD[/TD]
[TD]125689[/TD]
[TD]08-02-2018[/TD]
[/TR]
</tbody>[/TABLE]

Now in the above example the two rows are common and the field to be filtered is defined as column of excel. like a for employee code, B for name of employee. This will be added in the VBA form to be completed.

now the subject line is placed in Sheet 2 of same file where all the data is stored in column A is employee code, column B is EMAIL Address, Coloumn C is Generic subject (which needs to be modified i.e. it will be like - "Pending policy claims for". The Email body is to be hard coded which will be in multiple lines starting from say C3 to C15 (last value of C15 to be variable which can be set by the user). coloumn D is the CC Email ID and coloumn E is BCC Email ID

sheet 2 Column C16 to C20 will have the signature line.
Now the email should go like this

To: (Email address of Mr. A which is available somewhere in sheet 2 column B)
CC: (Email address of Mr. A which is available somewhere in sheet 2 column D)
BCC: (Email address of Mr. A which is available somewhere in sheet 2 column E)
subject: "Pending policy claims for A"
Email Body :
As per the details mentioned in sheet 2 from column C3 to C15 (variable)

then the table will be posted which may have many columns till a blank column is not coming.
Sample table Data will be

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee Code[/TD]
[TD]Name of the Employee[/TD]
[TD]Name of customer[/TD]
[TD]policy number[/TD]
[TD]Expiry Date[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]ABC[/TD]
[TD]Mr. XYZ[/TD]
[TD]123456[/TD]
[TD]10-03-2018[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]ABC[/TD]
[TD]Mr. EFD[/TD]
[TD]125689[/TD]
[TD]08-02-2018[/TD]
[/TR]
</tbody>[/TABLE]

Signature line as available in Sheet 2 column C3 to c15 is pasted as it is.

further the file is saved in excel format is a folder (folder name will be present date and time as say for eg. 10-03-2018 12:17:12 pm
this folder will have files named as A - (Subject Line)
This file will be attached to email
Email complete

All the emails will be send with display and the total number of email send is shown separately as pop up.

Can somebody please help me with this code.

 

[tdh777]

Hi.

There are numerous examples of VBA code to automate the sending of emails. Just google and find one you like.

Also consider that you might find this easier to do by putting the text of your letters into Word and doing a normal mail merge with Excel as the data Source.

Word has an extensive set of features in its inbuilt mail merge function that are designed to work with email and Excel.

Easier than re-inventing the wheel !

Cheers.

 

[bmkela2017]

Friend I already tried with. But issue is that the table which i I have to affix for some people it is of 2 rows for some it is 3 and the number of rows are variable.
Hence I am not able to do it so.
I googled with those details but was unable to identify such code which could be modified. If some body can help with this coding would be appreciable.
Thanks in advance.