autoresponder email

Posted on 2006-03-21
Last Modified: 2013-11-24
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,
Question by:C_Ave
    LVL 4

    Expert Comment

    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.

    Author Comment

      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,
    LVL 5

    Assisted Solution

    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)


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

                            MailMsg.Fields.Add("", cdoBasic);
                            MailMsg.Fields.Add("", username);
                            MailMsg.Fields.Add("", pw);
                            MailMsg.Fields.Add("", cdoSendUsingPort);
                            MailMsg.Fields.Add("", mailServer);
                            MailMsg.Fields.Add("", 10);
                            MailMsg.Fields.Add("", 25);
                            MailMsg.Fields.Add("", false);
                      catch (Exception ex)
                            Response.Write("<br>Email Not Sent");
                            Trace.Write (ex.ToString());



    Author Comment

    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,
    LVL 4

    Accepted Solution

    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("")
        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
        RecordsCount = 0
        Do Until rs.EOF
            RecordsCount = RecordsCount + 1
        NumberOfTables = Int(RecordsCount / 3)
        If RecordsCount / 3 > NumberOfTables Then NumberOfTables = NumberOfTables + 1
        'Begins initial loop for creating multiple tables in the word doc.
        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) = " "
                        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
            '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
                        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
                            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
            End With
        Next MultiTable
        'Close the Recordset and Connection.
        Set rs = Nothing
        'Add Terms & Conditions and salutations to document.
        With new_range
            .Collapse wdCollapseEnd
            .InsertAfter "TERMS & CONDITIONS" & vbCrLf & vbCrLf & Me!TermsAndConditions & _
                vbCrLf & vbCrLf & "Thanks," & vbCrLf
            .Font.Bold = True
        End With
        new_range.Collapse wdCollapseEnd
        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.InsertAfter Me!CustServRep
            new_range.Font.Bold = True
            new_range.InsertAfter Me!CustServRep
            new_range.Font.Bold = True
        End If
        new_range.Collapse wdCollapseEnd
        new_range.Collapse wdCollapseEnd
        new_range.InsertAfter "Notes:  Please visit" & _
            " 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
        'Sets default printer back to origional
        WordApp.ActivePrinter = UsersCurrentPrinter
        WordDoc.Close wdDoNotSaveChanges
        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"
            counter2 = counter2 + 1
            Me.Form.Caption = "  Creating PDF file: Timelaps Counter: " & counter2
        If Dir(sPDFfile) = Me!QuoteNo & ".pdf" Then
            MsgBox "   PDF File Created!" & Chr(13) & Chr(13) & "   Click email PDF"
            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 Sub

        MsgBox Err.Description
        Resume Exit_Command201_Click
    End Sub

    Author Comment

    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!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: HTML5 Specialist

    HTML5 development skills are critical to all developers. HTML5 is the foundation to almost any development process. That's why business, design studios, development shops and other organizations need HTML5 developers. Get your foot in the door as a HTML5 specialist.

    Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
    This is about my first experience with programming Arduino.
    This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now