SMTPClient Use Default Credentials

SSIS and Import/Export
1

Hi

I just solved why my send HTML email issue was erroring out, and now the code runs, but it's not sending the email to my box.

I have a basic send mail task that uses an SMTP Connection manager, and sends email fine. I've tried to replicate this in VB.net code, as I'm wanting to send by HTML, and it doesn't seem to be doing anything, but it runs without error.

If my send mail task uses windows authentication to send and it works, then surely using the same in script code should work there as well?

This is the code I'm using:

Public Sub Main()

    Dim htmlMessageTo As String
    Dim htmlMessageFrom As String
    Dim htmlMessageSubject As String
    Dim htmlMessageBody As String
    Dim smtpServer As String

    htmlMessageTo = Dts.Variables("User::HtmlEmailTo").Value.ToString()
    htmlMessageFrom = Dts.Variables("User::HtmlEmailFrom").Value.ToString()
    htmlMessageSubject = Dts.Variables("User::HtmlEmailSubject").Value.ToString()
    htmlMessageBody = Dts.Variables("User::HtmlEmailBody").Value.ToString()
    smtpServer = Dts.Variables("User::HtmlEmailServer").Value.ToString()

    SendMailMessage( _
    htmlMessageTo, htmlMessageFrom, _
    htmlMessageSubject, htmlMessageBody, _
    True, smtpServer)

    Dts.TaskResult = ScriptResults.Success
End Sub

Private Sub SendMailMessage( _
  ByVal SendTo As String, ByVal From As String, _
  ByVal Subject As String, ByVal Body As String, _
  ByVal IsBodyHtml As Boolean, ByVal Server As String)

    Dim htmlMessage As MailMessage
    Dim mySmtpClient As SmtpClient

    htmlMessage = New MailMessage( _
      SendTo, From, Subject, Body)
    htmlMessage.IsBodyHtml = IsBodyHtml

    mySmtpClient = New SmtpClient(Server)
    mySmtpClient.UseDefaultCredentials = True
    'mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials **<< commented this out and used "UseDefaultCredentails" instad to see if it worked, and it still never sent the email.**
    mySmtpClient.Send(htmlMessage)

End Sub

Private Sub MailMessage(htmlMessageTo As String, htmlMessageFrom As String, htmlMessageSubject As String, htmlMessageBody As String, IsBodyHtml As Boolean, smtpServer As String)
    Throw New NotImplementedException
End Sub

Not entirely sure what credentials it would be using as I sign in as an admin to the VS designer, but my email is a standard account?

Thanks
Andrew

2

I couldn't get this to work so ended up using a SQL Task to run email script and can use HTML that way instead:

DECLARE @body NVARCHAR(MAX)
DECLARE @email NVARCHAR(40)
set @email ='email@email.com'

SET @body ='<html><body><H3>File required</H3>
<p> Instructions to users</p>
<p>More instructions if needed</p>
</body></html>'


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'server_profile', 
@body = @body,
@body_format ='HTML',
@recipients = @email, 
@subject = 'Your email subject here'
3

Or you could use powershell or python