Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2008
    Posts
    25

    Unanswered: Simple ? Stupid! VBA Question

    Guys

    I need some basic VBA help!

    I have a button on a form, which, depending if the email address field is blank, I want two different actions to happen.

    Firstly, create the folder to store the file - regardless - works fine

    If the field is populated to open the report, save a copy and then email it - which it does fine.

    If the field is NOT populated, to open the report, save a copy and then print a hard copy.

    As I change through the records on the form, and click the button, if no email address is present, the routine fails.

    Any help and advice is greatly appreicated.

    Thanks for your time guys!




    Private Sub process_Click()


    stremailcc = "mike@CLIENT.co.uk" ' rejection email cc email address
    STRMTH = Format(DateSerial(Year(Date), Month(Date), 1), "mm")
    STRYR = Format(DateSerial(Year(Date), Month(Date), 1), "yyyy")
    strdy = Format((Now), "dd")
    strtm = Format((Now), "hh" & "mm" & "ss")

    On Error GoTo 1
    MkDir "\\KGBCSA02\cash4w\Cabinet\site\" & SiteID


    1

    If Dirty Then
    DoCmd.Save acForm, "issue details"
    Else
    End If
    RefreshDatabaseWindow


    If IsEmpty(EmailAddress) Then

    DoCmd.OpenReport "rpt issues", acViewReport, Me.SiteID = SiteID, "siteID = " & "'" & Me.SiteID & "'"
    DoCmd.PrintOut acPrintAll, , , acHigh
    DoCmd.OutputTo acReport, "rpt issues", acFormatPDF, "\\KGBCSA02\cash4w\Cabinet\site\" & Me.SiteID & "\" & STRYR & STRMTH & strdy & strtm & "-" & Me.SiteID & "-Handover Accepted Return.pdf", False
    strreportname = "\\KGBCSA02\cash4w\Cabinet\site\" & Me.SiteID & "\" & STRYR & STRMTH & strdy & strtm & "-" & Me.SiteID & "-Handover Accepted Return.pdf"

    Else

    DoCmd.OpenReport "rpt issues", acViewReport, Me.SiteID = SiteID, "siteID = " & "'" & Me.SiteID & "'"
    DoCmd.OutputTo acReport, "rpt issues", acFormatPDF, "\\KGBCSA02\cash4w\Cabinet\site\" & Me.SiteID & "\" & STRYR & STRMTH & strdy & strtm & "-" & Me.SiteID & "-3 IN 30 DAYS LTR.pdf", False
    strreportname = "\\KGBCSA02\cash4w\Cabinet\site\" & Me.SiteID & "\" & STRYR & STRMTH & strdy & strtm & "-" & Me.SiteID & "-3 IN 30 DAYS LTR.pdf"

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    strbody = "3 IN 30 DAYS " & Chr$(13) & Chr$(13) & "Please see attached document for further details. This document should be kept in the customer file until an approval has been given" & Chr$(13) & Chr$(13) & SiteID & " DO TO REPLY TO THE EMAIL." & Chr$(13) & Chr$(13) & Chr$(13) & "Thanks" & Chr$(13) & Chr$(13)
    On Error Resume Next

    With OutMail
    .SentOnBehalfOfName = "MIKE@CLIENT.co.uk"
    .To = EmailAddress
    .CC = stremail
    .BCC = ""
    .Attachments.Add strreportname
    .Subject = "3 IN 30 DAYS NOTIFICATION - Contract Number: " & SiteID
    .body = strbody
    .Display
    .send
    warning = False

    End With
    End If

    DoCmd.Close acReport, "rpt issues"


    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing

    DoCmd.OpenQuery "updateopenrecords"
    DoCmd.Requery
    DoCmd.Close acReport, "rpt issues"

    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If If EmailAddress is the name of a control bound to a column of the RecordSource object (or the name of a field in the form Recordset), this:
    Code:
    If IsEmpty(EmailAddress) Then
    will never work: Testing for Empty only works for Variant variables, not for the Value property of a control or of a Field object (contrarily to Excel). You must use:
    Code:
    If Nz(EmailAddress, "") = "" Then
    Have a nice day!

  3. #3
    Join Date
    Jan 2008
    Posts
    25
    Thanks Sinndho - worked like a dream!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •