August 22, 2017 at 8:58 am
Thom A - Tuesday, August 22, 2017 8:49 AMJeff Moden - Tuesday, August 22, 2017 8:37 AMThom,
I'm not sure where you'd set it up but couldn't you simply build a special email address for Outlook, setup a folder for each customer within Outlook that would actually point to the customer folders you were talking about for each customer, setup an "on arrival" rule, and have SQL Server include that in the Blind Copy address?Honestly, my knowledge of Outlook/Exchange isn't great, so not sure. If the folder needs to be set up manually, that'll be a problem as we have over 400,000 distinct customer references (not including their individual policies references, which they can have many of). Yes, not all of those clients/policies will be current, but it's still the large portion will be.
Hmmm... ok. BCC them to a common email address and have them save to a common folder. Then have someone write a "post processor" that regularly sweeps that common folder for some indication in the messages (TO email address) to figure out which folder to move them to. I've never opened a .msg file as a binary to see what's going on but it may be somethings as simple as a DOS FORFILES command or maybe a "simple" Powershell script that could do such a discriminatory move of the files.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 22, 2017 at 9:04 am
Jeff Moden - Tuesday, August 22, 2017 8:58 AMThom A - Tuesday, August 22, 2017 8:49 AMJeff Moden - Tuesday, August 22, 2017 8:37 AMThom,
I'm not sure where you'd set it up but couldn't you simply build a special email address for Outlook, setup a folder for each customer within Outlook that would actually point to the customer folders you were talking about for each customer, setup an "on arrival" rule, and have SQL Server include that in the Blind Copy address?Honestly, my knowledge of Outlook/Exchange isn't great, so not sure. If the folder needs to be set up manually, that'll be a problem as we have over 400,000 distinct customer references (not including their individual policies references, which they can have many of). Yes, not all of those clients/policies will be current, but it's still the large portion will be.
Hmmm... ok. BCC them to a common email address and have them save to a common folder. Then have someone write a "post processor" that regularly sweeps that common folder for some indication in the messages (TO email address) to figure out which folder to move them to. I've never opened a .msg file as a binary to see what's going on but it may be somethings as simple as a DOS FORFILES command or maybe a "simple" Powershell script that could do such a discriminatory move of the files.
This is a lot less crazy.
The thing is, to get what they want, they MUST go outside of SQL Server. Yeah, you can loop it back in, but you can't do it from within since SQL Server is managing databases, not emails. To get emails, you must go to the email management system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 22, 2017 at 9:04 am
Jeff Moden - Tuesday, August 22, 2017 8:58 AMThom A - Tuesday, August 22, 2017 8:49 AMJeff Moden - Tuesday, August 22, 2017 8:37 AMThom,
I'm not sure where you'd set it up but couldn't you simply build a special email address for Outlook, setup a folder for each customer within Outlook that would actually point to the customer folders you were talking about for each customer, setup an "on arrival" rule, and have SQL Server include that in the Blind Copy address?Honestly, my knowledge of Outlook/Exchange isn't great, so not sure. If the folder needs to be set up manually, that'll be a problem as we have over 400,000 distinct customer references (not including their individual policies references, which they can have many of). Yes, not all of those clients/policies will be current, but it's still the large portion will be.
Hmmm... ok. BCC them to a common email address and have them save to a common folder. Then have someone write a "post processor" that regularly sweeps that common folder for some indication in the messages (TO email address) to figure out which folder to move them to. I've never opened a .msg file as a binary to see what's going on but it may be somethings as simple as a DOS FORFILES command or maybe a "simple" Powershell script that could do such a discriminatory move of the files.
I do know that .msg files can be saved (i.e., you can save an Outlook email as a .msg file to a folder). So this post processor might actually be the way to go.
The SO used to be an Exchange Administrator. I'll run this by him and see if he has any suggestions on how this can be done. Probably the solution is an Exchange solution (even though Exchange is on SQL) with some sort of SQL Server table pointing to your NAS filer for each customer to search on the files (at a guess).
August 22, 2017 at 9:55 am
Thom,
I have a "silly question". Can the db_mail construct be manipulated to always send a cc of the .msg sent to the customer to a centralized mailbox? Then the message would be sitting in the mailbox and could be exported (as .msg) to "wherever", presumably. Just a thought...don't know if this would be too much manual work, but it would provide a location to have all the customer emails in the format you need. However, getting them "attached" in the 3rd part app to the right customer might be a real pain in the...anatomy. 🙂
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 22, 2017 at 11:28 am
Hugo Kornelis - Tuesday, August 22, 2017 5:20 AMAs you already found out, SQL Server does not create mail messages in .msg format. It sends message data to the Exchange Server, and the Exchange Server then creates the msg files (at least that's how I understand it, but everything that happens outside the SQL Server boundaries tends to get blurry for me).If you really want to go crazy on this, you could see if there is an API for the exchange server that you can use to query emails sent and retrieve them in .msg format. Or you could try if you can somehow directly access the Exchange database. But neither sounds like a very appealing option to me.
When you use the HTML option of sp_send_dbmail, then storing the HTML message content and producing that in a browser should result in a almost exact copy of the original email. (One of my customers in the past had a similar system, using SQL Server to send emails - I saved the content of the emails in a table and made them available to backoffice staff in case they needed to review the email history)
I like the direction you're heading. To solve this, I'd be looking at ways to dig the .msg files out of Exchange too.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 22, 2017 at 11:54 am
Phil Parkin - Tuesday, August 22, 2017 11:28 AMHugo Kornelis - Tuesday, August 22, 2017 5:20 AMAs you already found out, SQL Server does not create mail messages in .msg format. It sends message data to the Exchange Server, and the Exchange Server then creates the msg files (at least that's how I understand it, but everything that happens outside the SQL Server boundaries tends to get blurry for me).If you really want to go crazy on this, you could see if there is an API for the exchange server that you can use to query emails sent and retrieve them in .msg format. Or you could try if you can somehow directly access the Exchange database. But neither sounds like a very appealing option to me.
When you use the HTML option of sp_send_dbmail, then storing the HTML message content and producing that in a browser should result in a almost exact copy of the original email. (One of my customers in the past had a similar system, using SQL Server to send emails - I saved the content of the emails in a table and made them available to backoffice staff in case they needed to review the email history)
I like the direction you're heading. To solve this, I'd be looking at ways to dig the .msg files out of Exchange too.
You can absolutely query the HTML (or text) of the email body, subject, recipients, etc. from SQL Server. The only problem I see is the generation of the actual .msg file itself. It usually contains the email headers that the mail server places on the message. It doesn't exist in SQL Server because it didn't define the headers.
A number of years ago, I remember hearing of a SQL Server provider for Exchange. You had to have at least Exchange 2010, so I didn't really look into it very much because the odds of the company upgrading from Exchange 2003 were non-existent. I just looked it up and according to MS, it's no longer supported. Nonetheless, even if it were, I don't know if you could get SQL to tell Exchange to generate a .msg file.
August 22, 2017 at 12:07 pm
Grant Fritchey - Tuesday, August 22, 2017 9:04 AMJeff Moden - Tuesday, August 22, 2017 8:58 AMThom A - Tuesday, August 22, 2017 8:49 AMJeff Moden - Tuesday, August 22, 2017 8:37 AMThom,
I'm not sure where you'd set it up but couldn't you simply build a special email address for Outlook, setup a folder for each customer within Outlook that would actually point to the customer folders you were talking about for each customer, setup an "on arrival" rule, and have SQL Server include that in the Blind Copy address?Honestly, my knowledge of Outlook/Exchange isn't great, so not sure. If the folder needs to be set up manually, that'll be a problem as we have over 400,000 distinct customer references (not including their individual policies references, which they can have many of). Yes, not all of those clients/policies will be current, but it's still the large portion will be.
Hmmm... ok. BCC them to a common email address and have them save to a common folder. Then have someone write a "post processor" that regularly sweeps that common folder for some indication in the messages (TO email address) to figure out which folder to move them to. I've never opened a .msg file as a binary to see what's going on but it may be somethings as simple as a DOS FORFILES command or maybe a "simple" Powershell script that could do such a discriminatory move of the files.
This is a lot less crazy.
The thing is, to get what they want, they MUST go outside of SQL Server. Yeah, you can loop it back in, but you can't do it from within since SQL Server is managing databases, not emails. To get emails, you must go to the email management system.
Now this is entirely possible. Where I work, we use database mail a lot. We don't have code calling sp_send_dbmail directly. Instead, we have a wrapper procedure that takes a couple of job-specific parameters and does some other processing. You could very easily write one of these to support all the parameters of sp_send_dbmail that you need and have it add a BCC address to the @bcc parameter you pass along to sp_send_dbmail. Then, call your wrapper procedure instead of the original. This wouldn't be too rough.
One piece of advice if you go this route is that when you write your wrapper procedure, make sure you have defaults for as many parameters as you can except the minimum to send an email - profile, recipients and maybe subject. Then, when you call the wrapper procedure from wherever you're going to call it from, please use named parameters instead of relying on ordinal. It seems like something so simple until you have to add an optional parameter to your procedure and callers don't get updated before they're used.
August 22, 2017 at 2:55 pm
If you really have to so something like this, it makes more sense to copy the messages mailboxes (one mail box per customer has to be set up to hold the copies) by adding the appropriate maill address to the BCC parameter. OK, that doesn't save them as .msg files, but it's usually more convenient to have stuff in mailboxws than in directories full of .msg files. So you have one mailbox per customer to hold these copies. If you really want them in .msg files you should be ble to write something that gets outlook or whatever to save all the messages it receives for each or these mailboxes in a directory that matches the mailbox.
I've never done anything like this using Exchange because we banned our sql servers from interacting with Exchange as we couldn't afford to use anything as bug-riddled and performance-destroying as Exchange was in the early 2000s, but writing something to send email was trivial and later on it was even easier because MS released a function (with an Active-X interface, I think) to do it (but of course we had to either queue emails and service the queue in an agent job or use xp_cmdshell. I thought using SQL Agent would be less error-prone, although we had xp_cmdshell enabled on all servers, so tht's wht we did.
Tom
August 22, 2017 at 7:44 pm
Thom,
I might have found the answer for you and, believe it or not and despite the fact that all of the original code in the examples at the following URL are in VB, I'm pretty sure that you could actually do it all from SQL Server because I use CDOSYS for a whole lot of my "DBA Email" tasks right now.
Here's the link (awesome CDO resource, BTW).
http://www.cdolive.com/cdo5.htm
Once you get there, do a page search for the following items...
If you're interested, let me know and I'll give you a kickstart by posting my "CDOSendMail" proc. You'll pick up on how to translate the items at the link I provided to OLE Automation by looking at the proc, as well.
The cool part of doing it from SQL Server is that's where you're sending the email from to begin with. That also means that you know, right now, to which customer the email is addressed and so you don't have to read an email file to figure out which folder to put it in... you already know!
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2017 at 1:37 am
Jeff Moden - Tuesday, August 22, 2017 7:44 PMThom,I might have found the answer for you and, believe it or not and despite the fact that all of the original code in the examples at the following URL are in VB, I'm pretty sure that you could actually do it all from SQL Server because I use CDOSYS for a whole lot of my "DBA Email" tasks right now.
Here's the link (awesome CDO resource, BTW).
http://www.cdolive.com/cdo5.htm
Once you get there, do a page search for the following items...
If you're interested, let me know and I'll give you a kickstart by posting my "CDOSendMail" proc. You'll pick up on how to translate the items at the link I provided to OLE Automation by looking at the proc, as well.
The cool part of doing it from SQL Server is that's where you're sending the email from to begin with. That also means that you know, right now, to which customer the email is addressed and so you don't have to read an email file to figure out which folder to put it in... you already know!
Thanks Jeff, this does look promising, however, some of it does seem to be going over my head a little. As the provided code is in VB, I'm a little confused about how SQL Server would be able to execute such a task, or are we still talking about making a CLR here?
The resource does, however, have details on how to create a folder dynamically, which is great, as it may not always exist. I suppose my other question is, how do I get a mail item that is in a Exchange Folder to also appear on the network in a specific location. Is that another Outlook/Exchange feature, and you can configure folders to be mirrored to a directory?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 23, 2017 at 1:52 am
Jeff Moden - Tuesday, August 22, 2017 7:44 PMIf you're interested, let me know and I'll give you a kickstart by posting my "CDOSendMail" proc.
I am 🙂
Far away is close at hand in the images of elsewhere.
Anon.
August 23, 2017 at 2:42 am
Ok, for the moment I've implemented a bit of a "dirty" solution. I've created a VB.Net script which I've linked to an Outlook rule. Then, on a little VM we've set up, I leave Outlook open (permanently) which runs the mail rule on emails received from a specific email address, when it's BCC'd.
I'm still looking, however, to do this away from Mail rules, if possible. It relies on the VM running and Outlook responsive; that's too many extras in my opinion, which I'd like to avoid if possible. So, Jeff, I am still quite interested in your idea above.
For those interested, here is the VB.Net Macro:Public Sub WelcomeEmail(Item As Outlook.MailItem)
Const olMsg As Integer = 3
Dim strBranch As String
Dim strPolref As String
'Because TRY CATCH does not exist in VBA 2010
On Error GoTo Error
strBranch = Left(Right(Item.Subject, 13), 2)
strPolref = Left(Right(Item.Subject, 11), 10)
'strBranch = "00"
'strPolref = "PHAJ01CG01"
'MsgBox ("Email received for client " & strPolref & " On branch " & strPolref)
Dim strFilePath As String
strFilePath = "\\[ServerName]\[ShareName]\Branch" & Right(strBranch, 1)
If strBranch <> "00" Then
strFilePath = strFilePath & "\" & strBranch
End If
If Len(Dir(strFilePath, vbDirectory)) = 0 Then
MkDir (strFilePath & "\")
End If
strFilePath = strFilePath & "\" & Left(strPolref, 1)
If Len(Dir(strFilePath, vbDirectory)) = 0 Then
MkDir (strFilePath & "\")
End If
strFilePath = strFilePath & "\" & Left(strPolref, 2)
If Len(Dir(strFilePath, vbDirectory)) = 0 Then
MkDir (strFilePath & "\")
End If
Select Case Left(strPolref, 3)
Case "CON"
strFilePath = strFilePath & "\(" & Left(strPolref, 3) & ")"
Case "PRN"
strFilePath = strFilePath & "\(" & Left(strPolref, 3) & ")"
Case "AUX"
strFilePath = strFilePath & "\(" & Left(strPolref, 3) & ")"
Case "NUL"
strFilePath = strFilePath & "\(" & Left(strPolref, 3) & ")"
Case Else
strFilePath = strFilePath & "\" & Left(strPolref, 3)
End Select
If Len(Dir(strFilePath, vbDirectory)) = 0 Then
MkDir (strFilePath & "\")
End If
strFilePath = strFilePath & "\" & Left(strPolref, 4)
If Len(Dir(strFilePath, vbDirectory)) = 0 Then
MkDir (strFilePath & "\")
End If
strFilePath = strFilePath & "\" & Left(strPolref, 6)
If Len(Dir(strFilePath, vbDirectory)) = 0 Then
MkDir (strFilePath & "\")
End If
strFilePath = strFilePath & "\" & strPolref
If Len(Dir(strFilePath, vbDirectory)) = 0 Then
MkDir (strFilePath & "\")
End If
'can;t use System in Outlook VBA... -_-
'If (Not System.IO.Directory.Exists(strFilePath)) Then
'System.IO.Directory.CreateDirectory (strFilePath)
'End If
strFilePath = strFilePath & "\" & Item.Subject & ".msg"
'Test path
'strFilePath = "\\[ServerName]\[ShareName]\Branch3\03\X\XX\XXX\XXXX\XXXX01\XXXX01MR01\TestMail.msg"
Item.SaveAs strFilePath, olMsg
ExitSub:
Exit Sub
Error:
MsgBox (Err.Description)
GoTo ExitSub
End Sub
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 23, 2017 at 7:48 am
Thom A - Tuesday, August 22, 2017 4:07 AMTo start this off on "the right foot", I am pretty sure that this can't be achieved by T-SQL/SQL Server. It is, however, the task that I have been set. /sighTo give a little bit of an idea, certain directors in the company only understand that SQL Server as a Mailing System. It sends them emails with reports, so it can sends customers emails when they purchase something. What they don't seem to understand is that SQL Server is a data engine, and isn't really a mail merge tool. Of course, that argument falls on deaf ears.
So, at the moment, our SQL server sends a few emails to customers based on certain conditions (such as them buying a product, a renewal being due, etc). This, is achieve by a lovely thing called a CURSOR (that we all know and love), and does a nice RBAR process as it cycles through the customers to email (no triggers here, as the data is only a replication of the system's flat file system). It's "Beautiful" . :sick:
Anyway, a certain someone has now decided that in addition to the email being sent to the customer, an email needs to be saved to the customer's file. This needs to be done as a .msg file, so that they can view the file in Outlook and "know" it's an email.
I've had a little bit of a Google, but my Search-fu isn't even giving me results related to what I'm trying to do. This tells me, as I suspect, that the task at hand isn't possible with SQL Server, or at least not on its own. Does anyone know of a way to save the email that is sent via sp_send_dbmail? Specifically, as I said, it's needs to be in a .msg format.
I did consider taking that details out of msdb.dbo.sysmail_allitems, and then saving them off that way. The problem, however, is I don't see a way to export the data as a .msg file. I could save as (m)html, however, I doubt that's going to get the thumbs up; especially as header (to address, subject, sent date/time, etc) information would need to be displayed other ways which would normally be shown in the email file.
If this really can't be achieve, please, do not be afraid to shoot it down. In all honestly, I expect it to be and it gives me more reasons to go back to the Directors and tell them "SQL Server is not the right tool for this, we need a different client/service." Hopefully, if I get that through, they might actually open their wallets (as I doubt such tools are free, but not expensive).
Thanks for any help.
You haven't indicated what SMTP server SQL Server is using to send the emails. But since you mentioned Outlook I suspect it's Exchange.
But whatever it is -- Exchange, gmail, etc. -- the mail is sent using a specific account. The company could give needed users access to the email account. Users can be granted read-only access to an Exchange mailbox. I don't know if that's possible w/ other systems.
Alternatively, as others have suggested, CC or BCC one or more accounts for the relevant emails.
August 23, 2017 at 8:39 am
Thom A - Wednesday, August 23, 2017 1:37 AMJeff Moden - Tuesday, August 22, 2017 7:44 PMThom,I might have found the answer for you and, believe it or not and despite the fact that all of the original code in the examples at the following URL are in VB, I'm pretty sure that you could actually do it all from SQL Server because I use CDOSYS for a whole lot of my "DBA Email" tasks right now.
Here's the link (awesome CDO resource, BTW).
http://www.cdolive.com/cdo5.htm
Once you get there, do a page search for the following items...
If you're interested, let me know and I'll give you a kickstart by posting my "CDOSendMail" proc. You'll pick up on how to translate the items at the link I provided to OLE Automation by looking at the proc, as well.
The cool part of doing it from SQL Server is that's where you're sending the email from to begin with. That also means that you know, right now, to which customer the email is addressed and so you don't have to read an email file to figure out which folder to put it in... you already know!
Thanks Jeff, this does look promising, however, some of it does seem to be going over my head a little. As the provided code is in VB, I'm a little confused about how SQL Server would be able to execute such a task, or are we still talking about making a CLR here?
The resource does, however, have details on how to create a folder dynamically, which is great, as it may not always exist. I suppose my other question is, how do I get a mail item that is in a Exchange Folder to also appear on the network in a specific location. Is that another Outlook/Exchange feature, and you can configure folders to be mirrored to a directory?
No. No CLR required. Just a little OLE Automation. I'll post some code after work tonight.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 23, 2021 at 4:59 am
If you want to open and view the (.msg) MSG file so you can try MSG Viewer Tool. This tool can help you to open MSG file and View. Also, it can convert MSG files in PDF, EML, and many more file formats & Email Clients. To get this tool Visit @ https://www.sysinfotools.com/freeware/msg-viewer.php
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply