autoresponder email

Hello experts,
  I have a real hot potato that I'm trying to get moving fast, so I'm assigning the highest points to this question.

I'm looking for software that will:

- Pull information from a database
- Put the information into a pdf format that I specify
- Send the pdf out to an email address that is located within the database
- Delay the email by 24 hours before sending

  Please help!

Best regards,
Chad
C_AveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

abbdanCommented:
Don't know if there is any software out there but it shouldn't be too hard to create this whole thing using Microsoft Office Access 2000 and up.

You can store your information and email address data in tables in Access.
Create a module in Access to generate the document using the Acrobat Distiller reference
' you must have Acrobat Distiller installed to have the reference available to use.
Create an AutoExec Macro that runs your module
Use windows Schedule Tasks to run your database once every 24 Hours.

Create a form to populate your information table and either add your email addresses directly to the address table or use a form.  Could incorporate both into one form using a subform to host the list of email addresses.
0
C_AveAuthor Commented:
Abbdan,
  Wow... good stuff!  It's gonna take me a little bit, though, to digest and understand your suggestion.  I put up another post after I've done so, as I'm pretty sure I will have a question about your suggestions.
  I really appreciate your response.  Talk to you soon.

Best regards,
Chad
0
t_itaniumCommented:
Hi C_Ave

you can do it in another way..
if you want to use your programming skills, you can write a code to extract information from database, arrange them in the right format, and create atable in the database to store the email and the creation time...
then you have to make the software check this table continously and when ever the difference between the current time and the tiime in the database is 24 hours...then call the function that send the email to the specified email address..

You can use: (for example this function in c#) to send the email

private void EmailSend(string Sender, string message,string title)
            {

                  try
                  {

                        MailMessage MailMsg = new MailMessage();
                        string MessageBody;
                        MessageBody = "<html><body>";
                        MessageBody = message;
                        MessageBody = MessageBody + "</body></html>";
                        MailMsg.From = "From@email.com";
                        MailMsg.To = "to@email.com";
                        MailMsg.BodyFormat = MailFormat.Html;
                        MailMsg.Subject = title;
                        MailMsg.Body = MessageBody;
            
                        int cdoBasic = 1;
                        int cdoSendUsingPort = 2;
                        string username = "sender@email.com";
                        string pw = "senderpassword";
                        string mailServer = "smtpserver";


                        MailMsg.Fields.Add("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate", cdoBasic);
                        MailMsg.Fields.Add("http://schemas.microsoft.com/cdo/configuration/sendusername", username);
                        MailMsg.Fields.Add("http://schemas.microsoft.com/cdo/configuration/sendpassword", pw);
                        MailMsg.Fields.Add("http://schemas.microsoft.com/cdo/configuration/sendusing", cdoSendUsingPort);
                        MailMsg.Fields.Add("http://schemas.microsoft.com/cdo/configuration/smtpserver", mailServer);
                        MailMsg.Fields.Add("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout", 10);
                        MailMsg.Fields.Add("http://schemas.microsoft.com/cdo/configuration/smtpserverport", 25);
                        MailMsg.Fields.Add("http://schemas.microsoft.com/cdo/configuration/smtpusessl", false);
                        SmtpMail.Send(MailMsg);
                  }
                  catch (Exception ex)
                  {
                        Response.Write("<br>Email Not Sent");
                        Trace.Write (ex.ToString());
                  }

            }

cheers
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

C_AveAuthor Commented:
Hello t_itanium,
  Thank you for your response as well!!!

  Any idea how to throw the info into a pdf, using code, prior to sending with the automation process that you discussed above?

Best regards,
Chad
0
abbdanCommented:
I used this code to do something similar.. but I used Lotus Notes email instead of Access's built in DoCMD.SendMail which would have been alot simpler.  I also did this in the codebehind of a form but your module code could use similar techniques.  Your welcome to take from this code to build your own code.  What it does is creates a word doc using various techniques and then converts the word doc postscript file to the pdf.

Private Sub CreatePDF()
'This procedure creates the word document based on information in the form.
    Dim oConverter As ACRODISTXLib.PdfDistiller
    Dim sPSfile, sPDFfile As String
    Dim rs As New ADODB.Recordset
    Dim new_field As ADODB.Field
    Dim strSQLQuery As String
    Dim txt, locsignature, UsersCurrentPrinter As String
    Dim WordApp As Word.Application
    Dim WordDoc As Word.Document
    Dim new_range As Range
    Dim stFieldName(25) As String
    Dim stData(25, 10) As String
    Dim counter, x, y, t, MultiTable, NumberOfTables, _
        RecordsCount As Integer
    Dim counter2 As Double

   
    'assigns location of signature files to variable
    locsignature = "k:\signatures\"
   
    'Creates the application and opens the quote template file.
    Set WordApp = New Word.Application
    Set WordDoc = WordApp.Documents.Add("Quote.dot")
    Set oConverter = New ACRODISTXLib.PdfDistiller
   
    'Adds verbage to the table in the quote template.
    WordDoc.Bookmarks("Customer").Range.Text = Me!Customer
    WordDoc.Bookmarks("Reference").Range.Text = Me!Reference
    WordDoc.Bookmarks("Date").Range.Text = Format(Me!MyDate, "mmmm d, yyyy")
    WordDoc.Bookmarks("Quotation").Range.Text = Me!QuoteNo
    WordDoc.Bookmarks("CustomerName").Range.Text = Me!CustomerName
    If Me![Tech Notes] <> "" Then
        WordDoc.Bookmarks("TechNote").Range.Text = Me![Tech Notes]
    End If
   
    'Opens Items Table and pulls all records to match quote number.
    strSQLQuery = "SELECT * FROM [Items Table] WHERE [Quote No] = '" & Me!QuoteNo & "';"
    rs.Open strSQLQuery, CurrentProject.Connection
       
    'Adds the row containing the field names to stFieldName array.
    counter = 0
    For Each new_field In rs.Fields
        stFieldName(counter) = new_field.Name
        counter = counter + 1
    Next new_field
   
    'Gets total number of records in the recordset
    rs.MoveFirst
    RecordsCount = 0
    Do Until rs.EOF
        RecordsCount = RecordsCount + 1
        rs.MoveNext
    Loop
    NumberOfTables = Int(RecordsCount / 3)
    If RecordsCount / 3 > NumberOfTables Then NumberOfTables = NumberOfTables + 1
   
    'Begins initial loop for creating multiple tables in the word doc.
    rs.MoveFirst
    RecordsCount = 0
    For MultiTable = 1 To NumberOfTables
        y = 0
        Do Until (rs.EOF Or y = 3)
            For x = 0 To rs.Fields.count - 1
                If IsNull(rs(stFieldName(x))) Then
                    stData(x, y) = " "
                Else
                    stData(x, y) = rs(stFieldName(x))
                End If
                If x = 0 Then stData(x, y) = "Item " + CStr(RecordsCount + 1)
                If x = 0 And (rs("Quote Header") <> "" Or Not IsNull(rs("Quote Header"))) Then
                    stData(x, y) = rs("Quote Header")
                    RecordsCount = RecordsCount - 1
                End If
            Next x
            y = y + 1
            RecordsCount = RecordsCount + 1
        rs.MoveNext
        Loop
       
        'Cross tabs all the array information into the txt string.
        txt = ""
        For x = 0 To 22
            If x <> 2 Then
                If x = 0 Then
                    txt = txt & stFieldName(x) & " " & Me!QuoteNo
                Else
                    txt = txt & stFieldName(x)
                End If
                For t = 0 To y - 1
                    If x = 14 Or x = 18 Or x = 21 Then
                        txt = txt & vbTab & FormatCurrency(stData(x, t), 0) 'vbtab
                    Else
                        txt = txt & vbTab & stData(x, t) 'vbtab
                    End If
                Next t
                txt = txt & vbCrLf
            End If
        Next x
       
        'Make a Range at the end of the Word document.
        Set new_range = WordDoc.Range
        new_range.Collapse wdCollapseEnd
   
        'Insert the text and convert it to a table.
        new_range.InsertAfter txt
        new_range.ConvertToTable vbTab
   
        'Addtional formating for Table and cells and fonts.
        With new_range.Tables(1)
            .AutoFormat wdTableFormatList7, , False, , False, , , , False, True
            .AllowPageBreaks = False
            .Rows(1).Shading.BackgroundPatternColor = wdColorBlack
            .Rows(15).Shading.BackgroundPatternColor = wdColorBlack
            .Rows(14).Range.Font.Bold = True
            .Rows(14).Range.Font.Color = wdColorRed
            .Rows(18).Range.Font.Bold = True
            .Rows(18).Range.Font.Color = wdColorRed
            .Rows(21).Range.Font.Bold = True
            .Rows(21).Range.Font.Color = wdColorRed
            .Cell(14, 1).Range.Font.Color = wdColorBlack
            .Cell(18, 1).Range.Font.Color = wdColorBlack
            .Cell(21, 1).Range.Font.Color = wdColorBlack
            .Range.Font.Name = "Arial"
            .Range.Font.Size = 10
        End With
        With new_range
            .Collapse wdCollapseEnd
            .InsertParagraph
        End With
    Next MultiTable
   
    'Close the Recordset and Connection.
    rs.Close
    Set rs = Nothing
   
    'Add Terms & Conditions and salutations to document.
    With new_range
        .Collapse wdCollapseEnd
        .InsertParagraph
        .InsertAfter "TERMS & CONDITIONS" & vbCrLf & vbCrLf & Me!TermsAndConditions & _
            vbCrLf & vbCrLf & "Thanks," & vbCrLf
        .Font.Bold = True
    End With
    new_range.Collapse wdCollapseEnd
    new_range.InsertParagraph
    If Dir(locsignature & Me!CustServRep & ".jpg") = Me!CustServRep & ".jpg" Then
        new_range.InlineShapes.AddPicture locsignature & Me!CustServRep & ".jpg", False, True
        new_range.Collapse wdCollapseEnd
        new_range.InsertParagraph
        new_range.InsertAfter Me!CustServRep
        new_range.Font.Bold = True
    Else
        new_range.InsertAfter Me!CustServRep
        new_range.Font.Bold = True
    End If
   
    new_range.Collapse wdCollapseEnd
    new_range.InsertParagraph
    new_range.Collapse wdCollapseEnd
    new_range.InsertParagraph
    new_range.InsertAfter "Notes:  Please visit http://www.abb.com/electricalcomponents" & _
        " to see our products, test reports and other information."
       
    'Finds all ~ in the document and replaces them with "" which is a word doc CR/LF
    Set myrange = WordDoc.Content
    myrange.Find.Execute FindText:="~", _
    ReplaceWith:="", Replace:=wdReplaceAll
   
    'this will printout the .doc to a postscript file (.ps)
    sPSfile = "C:\" & Me!QuoteNo & ".ps"
    sPDFfile = "C:\" & Me!QuoteNo & ".pdf"
    WordApp.Visible = True
    UsersCurrentPrinter = WordApp.ActivePrinter 'records origional default printer settings
    WordApp.ActivePrinter = "Adobe PDF"
    WordDoc.PrintOut False, False, , sPSfile, , , , , , , True 'WordDoc.PrintOut False, False, , sPSfile, , , , , , , True
   
    Do Until Dir(sPSfile) = Me!QuoteNo & ".ps"
    'do nothing
    Loop
   
    'Sets default printer back to origional
    WordApp.ActivePrinter = UsersCurrentPrinter
   
    WordDoc.Close wdDoNotSaveChanges
    WordApp.Quit
    oConverter.FileToPDF sPSfile, sPDFfile, ""      'converts the .ps file to .pdf
    Kill sPSfile    ' delete the temporary .ps file
    Do Until Dir(sPDFfile) = Me!QuoteNo & ".pdf" Or Dir("C:\" & Me!QuoteNo & ".log") = Me!QuoteNo & ".log"
        'DoEvents
        counter2 = counter2 + 1
        Me.Form.Caption = "  Creating PDF file: Timelaps Counter: " & counter2
    Loop
    If Dir(sPDFfile) = Me!QuoteNo & ".pdf" Then
        MsgBox "   PDF File Created!" & Chr(13) & Chr(13) & "   Click email PDF"
    Else
        MsgBox "   There was an error in creating the PDF file"
        Call Shell("NotePad c:\" & Me!QuoteNo & ".log")
    End If
     
    'Close and set all opened objects to null.
    Set new_range = Nothing
    Set WordApp = Nothing
    Set WordDoc = Nothing
   
End Sub

Private Sub Command201_Click()
On Error GoTo Err_Command201_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Quote Form Components"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "Quote Form Bushings", acSaveNo

Exit_Command201_Click:
    Exit Sub

Err_Command201_Click:
    MsgBox Err.Description
    Resume Exit_Command201_Click
   
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
C_AveAuthor Commented:
Hello abbdan & t_itanium,
  I appreciate your responses to my question... I actually found a good way of doing this in php and utilizing fpdf functions, but wanted to close this thread and award your points.  I split the points evenly since you both had very good suggestions.  I appreciate your assistance!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java EE

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.