I am trying to send an automated email with pdf attachment of SSRS report created using SQL script. So far I have tried below but it seems like the pdf attachment is not direct option. Any suggestion on this scenario?

1. DB Mail – This works fine and attachment is getting connected via email. But it doesnt support pdf.
2. SSRS subscription – For some reason the ‘Email’ option is not visible under subscription tab and I don’t see how to enable it even via SSCM.

Below is my stored procedure-

`CREATE PROCEDURE usp_Test5
AS
BEGIN
— Step 1: Declare variables to store the result set
DECLARE @result NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);

-- Step 2: Define the query
SET @query = '
SELECT DISTINCT 
  itm.description,
  hum.type, 
  itm.display_item_number,
  sto.item_number, 
  sto.hu_id, 
  sto.lot_number, 
  sto.actual_qty, 
  sto.sto_id,   
  ord.original_line_number, 
  orm.carrier, 
  orm.service_level, 
  orm.store_order_number, 
  ord.original_order_number, 
  hum.control_number, 
  whse.wh_id, 
  whse.name, 
  whse.city, 
  whse.state, 
  whse.zip, 
  whse.addr1, 
  orm.ship_to_name, 
  orm.ship_to_addr1, 
  orm.ship_to_city, 
  orm.ship_to_state, 
  orm.ship_to_zip, 
  itm.unit_weight AS ItmUnitWeight, 
  ord.unit_weight AS OrdUnitWeight, 
  cont.weight AS ContainerWeight,
  itm.sq_ft,
  hum.parent_hu_id
FROM 
  dbo.t_hu_master AS hum 
  INNER JOIN dbo.t_whse AS whse ON hum.wh_id = whse.wh_id 
  INNER JOIN dbo.t_stored_item AS sto ON hum.wh_id = sto.wh_id 
  AND hum.hu_id = sto.hu_id 
  INNER JOIN dbo.t_order AS orm ON hum.wh_id = orm.wh_id 
  AND hum.control_number = orm.order_number 
  LEFT OUTER JOIN dbo.t_container AS cont ON hum.wh_id = cont.wh_id 
  AND hum.container_type = cont.container_type 
  INNER JOIN dbo.t_order_detail AS ord ON whse.wh_id = ord.wh_id 
  AND orm.order_number = ord.order_number 
  INNER JOIN dbo.t_item_master AS itm ON sto.wh_id = itm.wh_id 
  AND sto.item_number = itm.item_number 
WHERE orm.client_code IN (''TECHGYM'',''TECHCON'') 
AND orm.status = ''D''
ORDER BY hum.control_number';

-- Step 3: Send the email with the query result
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '<>', -- Replace with your Database Mail profile name
    @recipients = '<>', -- Replace with the client's email address
    @subject = 'TechnoGym Pick List Report',
    @body = 'Please find the attached Pick List report.',
    @query = @query,
    @execute_query_database = 'AAD', -- Specify your database name here
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'PickList.csv', -- PDF conversion not directly supported
    @query_result_header = 1, 
    @query_result_separator = ';', 
    @query_result_no_padding = 1,
    @query_result_width = 32767;

END
`

My Email setup and SSMS server job works fine but the attachment is weird. There is no way easy to attach a pdf with results of stored procedure.