VBA Loop to create Excel Sheet, save excel sheet to specific folder on computer and generate Outlook 365 Email with attached excel sheet.

[dan24]

Hi All

I am hoping you can assist in how i would be able to writeup a VBA macro code that can resolve the below issue.

Summary: Looking to create a VBA macro that can check the vins in the attached sample excel sheet “MOND94 – BUGS CERT -Checklist” where it will identify each row through a VBA Loop to determine if I need to follow up load port and ask them using a customized message generated in Microsoft Outlook 365 to send certificates to me for that missing vin for that particular load port.

Each Vin in Column H would have an identifier next to it in Column I to determine if a certificate needs to be followed up as follows:

Y = Yes vin needs to be treated and no need to follow up certificate as you already have it on file.

N = Vin does not need to be treated as it is coming from a non risk country and no need to follow up certificate.

Blank = Vins need to be checked and certificate for treatment needs to be followed up.




Here are the below issues currently faced.

Issue 1: The VBA macro would need to start in sheet ‘Rpt_Discharge_Excel’ in Column H (VIN) row 1 and start searching from top to bottom for the Vin Numbers.

Next to Column H (VIN) is Column I (Bugs Treated) if cell is blank then I want the macro to generate a sheet for that load port in Column C pasting that entire row for that missing blank VIN in a sperate load port sheet.

Each load port would have an email address that I need to send to as found in the Load Port Sheet and also I need to save the load port sheet in a centralized folder in documents (It just needs to be in 1 folder) so that I can refer back to it if needed. Please note if I were to run the macro again I would like for the sheet when saved in a folder not to overwrite the current excel file but rather save the latest sheet based on date and time.


Issue 2: Would like to have an Outlook 365 Email generated with the attached excel Load Port sheet for those missing vins with customs message saying.
“Hi All
Please find attached excel sheet to send us certificates for the following vins”


Issue 3: The macro needs to know when the last row is so that it doesn’t keep looping infinitely and needs to stop at the last row in sheet ‘Rpt_Discharge_Excel’
Notes:

• This macro would need to use some kind of do until loop or do loop, with if and else statements.
• Vins can range from 17 digits to 4 digits.

MOND94 - BUGS CERT - Checklist.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Voyage	Disch. Port	Load Port	Book No	BL no	Customer	Book Desc.	VIN	Bugs Treated?	Reason for Non-Treatment	Checker	Emailed?
2	MOND/94	AUMEL	DEBRV	AP002254	FDEG5V94BRAD7014	OPSKM	OPSKM OCTAVIA	UMBKU0NX7MY132456	Y		1
3	MOND/94	AUBNE	DEBRV	BP002244	FDEG5V94BRAE7002	OPAUNI	OPAUNI Q3 - BBM	XAUZZZF30M1117206	Y		1
4	MOND/94	AUBNE	DEBRV	BP002244	FDEG5V94BRAE7002	OPAUNI	OPAUNI Q3 - BBM	XAUZZZF31M1117103			1
5	MOND/94	AUBNE	BEANR	CE038629	FDEG5V94ANAE0001	BEFORM	PUM 5 DOOR SUV HATCH	XF02XXERK2MM09037	Y		1
6	MOND/94	AUBNE	BEANR	CE038629	FDEG5V94ANAE0001	BEFORM	PUM 5 DOOR SUV HATCH	XF02XXERK2MM89538			1
7	MOND/94	AUBNE	BEANR	CE038629	FDEG5V94ANAE0001	BEFORM	PUM 5 DOOR SUV HATCH	XF02XXERK2MP13458	Y		1
8	MOND/94	AUMEL	ESSDR	FS018073	FDEG5V94SRAD0001	ESDAM	Fuso Truck	UYAFEB7UERDA10101			1
9	MOND/94	AUMEL	ESSDR	FS018073	FDEG5V94SRAD0001	ESDAM	Fuso Truck	UYAFEB7UERDA10102			1
10	MOND/94	AUMEL	ESSDR	FS018073	FDEG5V94SRAD0001	ESDAM	Fuso Truck	UYAFEB7UERDA10103	Y		1
11	MOND/94	AUBNE	ITLIV	GS018234	FDEG5V94LIAE0002	ESITPRO	Cube conveyor	21CB02			1
12	MOND/94	AUBNE	ITLIV	GS018234	FDEG5V94LIAE0002	ESITPRO	Cube bifang	21CS03	Y		1
13	MOND/94	AUBNE	GBSOU	KE038289	FDEG5V94AMAE1000	BEHYS	Crane	K117E01576V	N	NON-RISK ORIGIN	0
14	MOND/94	AUBNE	GBSOU	KE038289	FDEG5V94AMAE1000	BEHYS	Forklift	K117E01576V-3	N	NON-RISK ORIGIN	0
15	MOND/94	AUBNE	NLAMS	ZE038290	FDEG5V94AMAE1001	BEHYS	Mast	K117E01577V-2	Y		1
16	MOND/94	AUBNE	NLAMS	ZE038290	FDEG5V94AMAE1001	BEHYS	Boom fork lift	K117E01577V-1	Y		1
17	MOND/94	AUBNE	NLAMS	HE038294	FDEG5V94AMAE1002	BEHYS	Reachstaker	K117E01580V	Y		1
18	MOND/94	AUBNE	GBNCL	CE038504	FDEG5V94NCAE1000	BENCEUK	NISSAN QASHQAI	SJNFBAJ11A2985897	N	NON-RISK ORIGIN	0
19	MOND/94	AUBNE	GBNCL	CE038504	FDEG5V94NCAE1000	BENCEUK	NISSAN QASHQAI	SJNFBAJ11A2990966	N	NON-RISK ORIGIN	0
20	MOND/94	AUBNE	GBNCL	CE038504	FDEG5V94NCAE1000	BENCEUK	NISSAN QASHQAI	SJNFBAJ11A2991067	N	NON-RISK ORIGIN	0
Rpt_Discharge_Excel

Cell Formulas
Range	Formula
K2:K20	K2	=COUNTIF('Data Dump'!A:A,Rpt_Discharge_Excel!H2)

MOND94 - BUGS CERT - Checklist.xlsm
	A	B	C	D
1	VIN	Checker		Data Dump
2	UMBKU0NX7MY132456	1		UMBKU0NX7MY132456
3	XAUZZZF30M1117206	1		XAUZZZF30M1117206
4	XAUZZZF31M1117103	1		XAUZZZF31M1117103
5	XF02XXERK2MM09037	1		XF02XXERK2MM09037
6	XF02XXERK2MM89538	1		XF02XXERK2MM89538
7	XF02XXERK2MP13458	1		XF02XXERK2MP13458
8	UYAFEB7UERDA10101	1		UYAFEB7UERDA10101
9	UYAFEB7UERDA10102	1		UYAFEB7UERDA10102
10	UYAFEB7UERDA10103	1		UYAFEB7UERDA10103
11	21CB02	1		21CB02
12	21CS03	1		21CS03
13	K117E01577V-2	1		K117E01577V-2
14	K117E01577V-1	1		K117E01577V-1
15	K117E01580V	1		K117E01580V
Data Dump

Cell Formulas
Range	Formula
B2:B13	B2	=COUNTIF(Rpt_Discharge_Excel!H:H,'Data Dump'!A2)
A2:A15	A2	=LEFT(D2,17)

MOND94 - BUGS CERT - Checklist.xlsm
	A	B	C	D	E
1	Load Port	Countries	Level of Risk	Email	Save Folder To
2	DEBRV	Bremerhaven	High Risk Country	johnsmith@gmail.com;	documents\bugs
3	BEANR	Antwerp	High Risk Country	helloworld@gmail.com; jaydensmith@outlook.com;	documents\bugs
4	ESSDR	Santander	High Risk Country	jacksmith@hotmail.com;	documents\bugs
5	ITLIV	Livorno	High Risk Country	jamesmay@gmail.com; Jeremeyc.outlook.com; rh@gmail.com;	documents\bugs
6	NLAMS	Amsterdam	High Risk Country	danjames@outlook.com;	documents\bugs
7	GBSOU	Southampton	Non Risk Country	ryanmike@outlook.com;	documents\bugs
8	GBNCL	Newcastle	Non Risk Country	bobsmith@gmail.com;	documents\bugs
Load Port

MOND94 - BUGS CERT - Checklist.xlsm
	A	B
1	Disch. Port	Port
2	AUMEL	Melbourne
3	AUPKL	Port Kembla
4	AUBNE	Brisbane
Disch Port

Please advise if you can assist?

Best regards

Dan

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA Loop to create Excel Sheet, save excel sheet to specific folder on computer and generate Outlook 365 Email with attached excel sheet. - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.