search

farzinmonsef / testNEW

0 stars 0 forks source link

sendmail-outlook.vbs #10

Open farzinmonsef opened 8 years ago

farzinmonsef commented 8 years ago

Dim args, emailBody, emailRecipient, emailAttach, emailTitle, MsgAll, Resp '========================================================================================================== '========================================================================================================== '========================================================================================================== Sub getParameters Set args = WScript.Arguments if args.count=0 then msgbox "Usage:" & vbCrLf & " sendmail-outlook Title Body Attach Recipient/s" set args = Nothing Wscript.Quit end if if args.count>0 then emailTitle = args.Item(0) end if if args.count>1 then emailBody = args.Item(1) end if if args.count>2 then emailAttach = args.Item(2) end if if args.count>3 then emailRecipient = args.Item(3) end if MsgAll="Title: " & emailTitle & vbcrlf & "Body: " & emailBody & vbcrlf & "Attach:" & emailAttach _ & vbcrlf & "To: " & emailRecipient

set args = Nothing End Sub '======================================================= function testFile (path) Set fso = CreateObject("Scripting.FileSystemObject") If (fso.FileExists(path)) Then testFile = true Else testFile = false End If Set fso = nothing End Function '======================================================= Sub sendEmail(dispOrSend)
Dim outobj, mailobj
Dim strFileText Dim objFileToRead
Dim a, x

if( testFile(emailBody) )then if( emailRecipient <> "" )then Set outobj = CreateObject("Outlook.Application")
Set mailobj = outobj.CreateItem( 0 )
Set objFileToRead = CreateObject("Scripting.FileSystemObject").OpenTextFile( emailBody, 1 )
strFileText = objFileToRead.ReadAll() objFileToRead.Close Set objFileToRead = Nothing
With mailobj
.To = emailRecipient
.Subject = emailTitle
.Body = strFileText
if(emailAttach <> "") then a = Split( emailAttach, "|" ) for each x in a if( testFile( x ) )then .Attachments.Add x End if next end if if dispOrSend = 1 then .Send else .display
end if End With 

  'Clear the memory
  Set outobj = Nothing    
  Set mailobj = Nothing   
else 
  msgbox "Recipient NOT FOUND"
end if

else msgbox "File to read email text NOT FOUND" & vbcrlf & emailBody end if End Sub '========================================================================================================== '========================================================================================================== '========================================================================================================== getParameters

Resp = MsgBox(MsgAll & vbCrlf & vbCrlf & vbCrlf & "Yes = Review Email" & vbCrlf & "No = Immediately Send" & vbCrlf & "Cancel = Cancel Sending eMail" & vbCrlf, 3 + 32)'"Review email before sending?",

if Resp = 6 then 'Yes was clicked, user wants to review email first sendEmail 0 elseif Resp = 7 then 'If no is clicked, send immediately sendEmail 1 elseif Resp = 2 then 'If Cancel is clicked
End if

farzinmonsef commented 8 years ago

cscript.exe sendmail-outlook.vbs "TestMail***" "C:\Users\zkpl9e1\Downloads\Program\SendMail\eMail.txt" "C:\Users\zkpl9e1\Downloads\Program\SendMail\CW-Tutorial - 01.jpg|C:\Users\zkpl9e1\Downloads\Program\SendMail\schedule.Log" "farzin.Monsef@siteName.com"

farzinmonsef commented 8 years ago

USP_SendReportMail.sql

USE [......] GO /\ Object: StoredProcedure [dbo].[USP_SendReportMail] Script Date: 6/21/2016 2:19:29 **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

ALTER PROCEDURE [dbo].[USP_SendReportMail]
@EmailMessage varchar(MAX), @RecepientKey varchar(500), @EmailSubject varchar(200), @CCListParam varchar(500)='' AS
BEGIN TRY
DECLARE @ToList VARCHAR(500),@CCList VARCHAR(50),@profile_name Varchar(100),@MailPriority Varchar(6)='Normal', @Subject VarChar(255), @StatusMessage VarChar(100) ; 

    print '@EmailSubject='+@EmailSubject

    Set @profile_name =(SELECT ConfigValue FROM dbo.AppSettings Nolock WHERE CONFIGNAME = 'MailProfileName');
    if (CHARINDEX(';', @RecepientKey)>0)or(CHARINDEX(',', @RecepientKey)>0)
    Begin
        Set @ToList =@RecepientKey;
        SET @CCList = @CCListParam;     
    End
    else
    begin
        Set @ToList =(SELECT ConfigValue FROM dbo.AppSettings Nolock WHERE CONFIGNAME = @RecepientKey );
        /* Defaulting to Dev group. This needs to be Dynamic */  
        SET @CCList = 'dg.usesr@siteName.   com';  
    end

    DECLARE @ComputerName VARCHAR(50) = @@SERVERNAME ;          
    set @EmailSubject =   @ComputerName + '-' + @EmailSubject

    EXEC msdb.dbo.sp_send_dbmail  
        @profile_name = @profile_name,  @copy_recipients = @CCList , @recipients = @ToList,  @body = @EmailMessage , @importance=@MailPriority,  @subject =  @EmailSubject ,@body_format = 'HTML';  
    END try
 BEGIN catch   
      /* What needs to be handled here ? */ 
 END catch;
farzinmonsef commented 8 years ago

USP_GenReport_ImportStatus.sql

USE [....] GO /\ Object: StoredProcedure [dbo].[USP_GenReport_DailyImportStatus] Script Date: 8/9/2016 3:27:41 **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON

GO

-- ============================================= -- Description: For generating Report in DailyImportStatus table -- generating report STATUS=3 -- Then LOG all total counts for Tables: -- 1. Check (Job) -- ============================================= ALTER PROCEDURE [dbo].[USP_GenReport_DailyImportStatus] @dateSelected date AS BEGIN SET NOCOUNT ON; truncate table DailyImportStatus

print '...'     
IF EXISTS (select top 1 1 from Import_Control (NOLOCK) where (File_Source = '...') AND (CONVERT(Date,Create_Start) = CONVERT(DATE,@dateSelected)) AND (Status = 3) )
BEGIN
    insert into DailyImportStatus([Source], [TranDate], [LoadedToday], [TotalInSystem], [CreateDate], [comment])
    --declare @dateSelected date
    --set @dateSelected = '2016-06-09'
    select '...'as [Source], Convert(Date,isnull(Tran_Date,'')) 'Tran_Date', Count(*) As 'LoadedToday'
    ,(Select Count(*) from [dbo].[Check] (nolock) WHERE Convert(Date,isnull(Tran_Date,'')) = Convert(Date,isnull(A.Tran_Date,''))) As 'Total in System'
    , Convert(Date, isnull(Create_Date,'')), '' as comment
    --select top 10 *
    FROM [dbo].[Check] (nolock) A 
    WHERE Convert(Date,isnull(Create_Date,'')) = @dateSelected
    --order by Convert(Date, Created_Date) desc
    GROUP BY Convert(Date, isnull(Create_Date,'')), Convert(Date, isnull(Tran_Date,''));
END
ELSE
BEGIN
    INSERT INTO [dbo].[DailyImportStatus]([Source],[TranDate],[LoadedToday],[TotalInSystem],[CreateDate],[comment])values('...',@dateSelected,0,0,@dateSelected,'Not Imported Today')
END

END

farzinmonsef commented 8 years ago

USP_GenReport_HTML_ImportStatus.sql

USE [.....] GO /\ Object: StoredProcedure [dbo].[USP_GenReport_HTML_ImportStatus] Script Date: 6/21/2016 2:57:30 **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Description: For Creating HTML Report based on ImportStatus table -- =============================================
-- Exec [USP_MAIL_DATA_GenReport_ImportStatus] ALTER PROCEDURE [dbo].[USP_GenReport_HTML_ImportStatus] --drop PROCEDURE [dbo].[USP_MAIL_DATA_GenReport_ImportStatus]
@today DATETIME = null AS
BEGIN 

DECLARE @SUBJECT NVARCHAR(MAX)          
DECLARE @MESSAGE NVARCHAR(MAX)          

DECLARE @TRANCOUNT INT     
DECLARE @NITRANCOUNT INT        
DECLARE @TEMPLATE NVARCHAR(MAX)          

if (@Today is null) SELECT @Today=GETDATE()          
SET  @TRANCOUNT=0          

SELECT @SUBJECT = 'Imported Control Data - ' + convert(VARCHAR(10),@Today,101)           
SELECT @template = 'SELECT * FROM [DailyImportStatus] (nolock)'
DECLARE @Count INT          
SELECT @tranCount = @Count                 
SELECT @MESSAGE = '<html><head><style type="text/css">
.style1 {color: #ff0040; text-align: center;}/*red*/
.style2 {color: #8000ff; text-align: center;}/*velvet*/
.style3 {color: #4000ff; text-align: center;}/*darkblue*/
.style4 {text-align: center;}/*NoColor*/
.style5 {color: #969696;  text-align: center;}/*gray*/
.style6 {background-color: #B4F0F0; color: #4000ff; text-align: center;}/*darkblue With backgroundColor*/
.style7 {background-color: #B4F0F0; color: #000000; text-align: center;}/*darkblue With backgroundColor*/

.TFtable{
        width:100%; 
        /*border-collapse:collapse; */
        text-align: center;
        background: #b8d1f3;
    }
</style>
</head>
<body style="background-color: #DEDEDE;">
'
SELECT @MESSAGE =  @MESSAGE + '<font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#333333" >The Import Data for the <font color="#4000ff">P1</font>, <font color="#4000ff">P2</font>, <font color="#4000ff">P3</font>, <font color="#4000ff">P4</font> and <font color="#4000ff">FXCL </font> are as below for the feed date <font color="#4000ff">'+ convert(VARCHAR(10),@Today,101) + '</font>.<BR><BR>Please validate the records and do let us know in case of any issues.<BR><BR>'          
SET @MESSAGE = @MESSAGE + '<B>...</B><BR><BR>'    
DECLARE @body1 VARCHAR(max)          
                                                                        --  d6d0c4=gray     ffcccc=light red    'Light Cyan
SET @body1='

<table  class="TFtable" BORDER = 1 style="font-size:9pt;font-family:Helvetica" cellpadding="5">
<TR BGColor=#B4F0F0>
<td><b>Source</b></TD><td><b>Tran Date</b></td> <td><b>Imported Today</b></td> <td><b>Total in System After Today’s Load</b></td> <td><b>Create Date</b></td> <td><b>Comment</b></td> </TR>'          

SELECT @body1 = @body1 +'<TR><TD class="style7">'+isnull([Source], '')+'</TD>'+  
    +'<TD>'+convert(varchar(10),isnull([TranDate],''), 101)+'</TD>' 
    +'<TD class="style2">'+convert(varchar(10),isnull([LoadedToday], ''))+'</TD>'
    +'<TD class="style3">'+convert(varchar(10),isnull([TotalInSystem], ''))+'</TD>' 
    +'<TD class="style5">'+convert(varchar(10),isnull([CreateDate], ''), 101)+'</TD>'
    +'<TD>'+isnull([comment],' ')+'</TD></TR>' 
FROM [dbo].[DailyImportStatus]
SET @MESSAGE=@MESSAGE+ @body1+'</table>'          
SET @MESSAGE=@MESSAGE+ '</table><br>In case of any issues,<br>          
<a href="mailto:userGrp@SiteName.com?subject=Issues : '+ @SUBJECT +'">Mail To ...</a> <br>          
<br><br>          
*****END OF REPORT*****          
</font></body></Html>  ';

if isnull(@MESSAGE,'') <> ''
begin
    EXEC USP_SendReportMail @MESSAGE, 'DailyImportNotifyTo' , '... - Daily Data Load Notification'
END

END