Create excel files based on requirement and email automatically?

Hi all,

Hope all are well.

I am trying macro for below steps. but i get below error. Not sure where its going wrong.

Below are the requirements.

1. Copy all data in sheet "Source_Data" from Source file in a new excel workbook with sheet renamed as "Source Data".

2. Select all the data present in sheet "Source Data" and create a pivot table in a new sheet with pivot table name as "My Pivot". Rename this sheet as Pivot Table.

3. In pivot table My Pivot", keep ALERT_DATE (UTC) in field rows grouped by days, UNIQUE_ALERT_ID in field values, COUNTRY/REGION in rows field.

4. Save this new workbook as "Current Log - " followed by current date in YYYYMMDD format in user's downloads folder.

5. Attach this workbook in a new outlook mail with below content in the body of mail

"Dear all,

Please find attached.

Additionally, refer (Logs 2024) "

6. To field in outlook mail should contain below address

[email protected]

7. Cc field in outlook mail should contain below addresses

[email protected]
[email protected]

8. Subject of outlook mail shall be "Current Log - " followed by current date in YYYYMMDD format.

Note: Macro will be run from Source file from button in sheet Run

Source File is the one from which macro will run.

"Current Log - 20241015" is the result file.

Text in red is actually hyperlink but not sure how to keep it as hyperlink in macro.

Can someone please check and help why macro in source file is not working. Thanks in advance.

Still same for me. The new file is being created but the macro stops with aforementioned error code at the point as in below image

Does this work (selecting second cell, not first):

Please Login or Register  to view this content.

Originally Posted by rizwanulhasan

Also, i dont want mail to be sent automatically. It should be all ready but i should click the send button. So which line needs to be omitted

Try to comment this one and test it:

Please Login or Register  to view this content.

Here is set log link reference (change it in your code to exact path):

In comments are made changes:

Please Login or Register  to view this content.

Usedrange is unreliable (in your workbook it includes a lot of empty rows/columns.) Use:

Please Login or Register  to view this content.

in the previous line instead of

Please Login or Register  to view this content.